Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. Moptop

    text field with a number of values

    ...to process it as a recordset. Eg. Say that the text contains Record1|Record2|Record3 Then use the Replace function and a bit of string handling to make it <data><record>Record1</record><record>Record2</record><record>Record3</record></data> Then you could use the select * from openxml on the...
  2. Moptop

    Nearest in SQL

    For info, and just so that you can share my pain, here is the query that I am trying to fix: SELECT genpo, dealer, cast(sintModelYear AS varchar) + '_' + cast(modelID AS varchar) + '_' + cast(bodyID AS varchar) + '_' + cast(badgeID AS varchar) + '_' + cast(engineID AS varchar) + '_' +...
  3. Moptop

    Nearest in SQL

    ...record that was less than or equal - but what if there wasn't? I think that this query would work - but it gets complex pretty quickly. SELECT p.*, coalesce( (SELECT MAX([Year]) FROM Years y WHERE y.[Year] <= p.[Year]) , (SELECT MAX([Year]) FROM Years y WHERE y.[Year] > p.[Year])) AS...
  4. Moptop

    Nearest in SQL

    Hi How would you code a query in T-SQL where you were looking for the nearest match. As an example - assume that you have a Product table and a Year table. Each product has a year. However not every year is populated, in the Year table. Therefore assume that every year between 1980 and 1990...
  5. Moptop

    Problem with use of &quot;is null&quot;

    ...select i, replicate('Z', 1000) f into test from @t --delete from @t set nocount off print 'get total records (3 million)' exec('select count(*) from test') print 'add a new column and index' exec('alter table test Add [v] varchar(3) NULL') exec('create index i1 on test (v)') print...
  6. Moptop

    Locking !!

    Hello I'm not sure that this will help, but . . . We have issues with SQL2000SP3 where queries deadlock themselves with the "max degree of par..." message. We found that the problem is that SQL escalates locks to actually lock the physical areas of the disk. To work around the problem we have...
  7. Moptop

    Problem with use of &quot;is null&quot;

    Hi Thanks for your response. What do you mean by "no SQL2kSP3 going bonkers with parallelism". As you can see, when I force MAXDOP to 1, the query works fine. On another separate issue, we have been receiving deadlocks with Business Objects queries and we have had to set MAXDOP to 1 to stop...
  8. Moptop

    Problem with use of &quot;is null&quot;

    Hi Thanks both for coming back to me. The code above was merely an example to prove that the problem exists. In the example, there are 2172560 records with null in the field before the update. However, the update in this case updated 2350671 records. Sometimes, it updates a different number of...
  9. Moptop

    Problem with use of &quot;is null&quot;

    ...= 'No' from dbo.imp_tblPerson_1 p where p.Age > 0 and p.Age < 16 --(284374 row(s) affected) select GeneralRetailContactFlag, count(*) from dbo.imp_tblPerson_1 group by GeneralRetailContactFlag -- GeneralRetailContactFlag -- ------------------------ ----------- -- NULL...
  10. Moptop

    Audit Trails/Transactions Log

    Hello You could use triggers for this. Although most people frown on the use of triggers, I think that this is a perfectly reasonable use. The trigger would just have to compare the inserted and deleted pseudo tables and write differences away to an audit log. Please remember that any code...
  11. Moptop

    Confused SQL Developer

    Hi The DBA is thinking that there is probably a SQL Server issue. One of the DLLs has been corrupted and he is taking the server down to fix it. I guess that that explains it. It is kind of worrying though - It was just a routine check of the data that highlighted it. Thanks anyway Mark
  12. Moptop

    Confused SQL Developer

    ...by eliminating parts of the query that could be the problem. The original query is about 100 lines long. When I run this query: select count( * ) from dbo.imp_tblPerson p where (p.[HondaCustomerProspectIndicator] is null) and exists (select top 1 'x' from...
  13. Moptop

    Confused SQL Developer

    ...or less. The 2nd query was originally using EXISTS - but this had the same result. I am using SQL Server 2000. Here is the SQL: select count( * ) from dbo.imp_tblPerson p where p.[HondaCustomerProspectIndicator] is null Returns 1317608 records (i.e 1.3 million) select count( * )...
  14. Moptop

    How do get time of a datetime value using T-Sql

    You could do: print substring(cast(getdate() as varchar), 13, 7) But that does,'t give you "Seconds" -you could add DATEPART ( s, getdate()) to the string print substring(cast(getdate() as varchar), 13, 5) + ':' + str(DATEPART ( s, getdate()),2,0) + substring(cast(getdate() as varchar), 18...
  15. Moptop

    Saving a Stored Procedure

    Hi The routine is part of our Data Warehouse load where we add derived fields to tables. Step 1 - Create new database Step 2 - Insert all operation tables into new database Step 3 - Add extra columns and calc rerived fields. Step 4 - Take off clothes and dance naked around the mulberry...
  16. Moptop

    How do get time of a datetime value using T-Sql

    Hi You could use CONVERT ( varchar , @thedate , 8 ) This would give you hh:mm:ss Mark
  17. Moptop

    Saving a Stored Procedure

    I am having a problem with the T-SQL parser when creating a stored proc. The issue is that my stored proc adds a new field to a table and then updates the field. The parser fails due to the field not existing at the moment. Is there any way to bodge this to make the stored proc save. Here is...
  18. Moptop

    com4abap failures

    Hi We are having intermittent problems with com4abap. We are using VB6 com+ components called from SAP. What happens is this. - we start the service on our server - we check the connection from SAP - works fine - we check our ABAP application - works fine At some point over the next few days...
  19. Moptop

    Triggers - how to join INSERTED to DELETED

    All valid points, but I am thinking that with GUIDs I might get a few more tracking benefits when the data gets to our data warehouse as each record will have a unique key - BUT - it must be said that I haven't yet made the decision to do it this way yet. I will do some more investigation...
  20. Moptop

    Triggers - how to join INSERTED to DELETED

    Hi In our database, the primary keys change over the lifespan of the customer. for instance customer 1111 can become 2222 and then later on be renumbered to 3333. I am trying to come up with a plan for auditing data changes. My current plan is to add a GUID column to every table that is...

Part and Inventory Search

Back
Top