Search results for query: *

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...

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) + '_' +...

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...

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...

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 =...

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...

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...

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...

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...

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...

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

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...

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...

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...

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...

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

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...

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...

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...

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...