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

    A slightly funky way to do this is to format the text as XML and then 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...
  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

    Thanks for the response. This would work when there was always a 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...
  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;

    Hello I can recreate the problem with this script. It takes a little while to run, but it is interesting. Mark ------ set nocount on declare @t table (i int) print 'populate temp table with 3 million records' declare @count int set @count = 0 while @count < 3000000 begin if @count % 100000 =...
  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;

    Hello I have been getting some random errors when building our data warehouse. Generally, the sequence that we add a new field to the data warehouse is: 1. Add a new column 2. Build an index on the column 3. Set column to 'Yes' based on certain conditions 4. Set all values with NULL to 'No' The...
  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

    Hello Thanks for coming back to me. I am the only user logged on and I am looking at the same database. The problem gets stranger still. I am trying to isolate the problem by eliminating parts of the query that could be the problem. The original query is about 100 lines long. When I run this...
  13. Moptop

    Confused SQL Developer

    Hello I am having problems with a query that I am developing on our Data Warehouse. Briefly, the problem is this: When I cound the number of records in a table with a null value, I get 1.3 million. If I count the number of records with a null value AND some other clause, I get more records...
  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