March 28, 2003 at 11:28 am
Forgot to mention, when we first noticed this problem, we spent hours rebuilding indexes on the affected tables. It did not help. If this scenario matches your problem, try using MAXDOP 1 in your query before you go to the trouble of rebuilding indexes.
March 29, 2003 at 1:34 am
March 29, 2003 at 11:27 am
Sorry, I don't have a URL for this. One of our DBAs called MS about 6 wks. ago after we had were unable to troubleshoot this problem. She got the workaround solution (using MAXDOP 1) from MS. MS told her it was a recently reported bug. I just did a quick search against the KB but didn't find anything. Perhaps I didn't look hard enough. The same DBA called MS again last week. That's how we learned this will be fixed in SP4. The good news is, this only affects counting queries. The underlying data is fine. If I do SELECT * instead of count(*) with an IS NULL in the WHERE clause, I get the correct records returned without having to specify MAXDOP 1. BTW, this problem presented itself the day after we applied SP3 to a data warehouse server. After that night's load, my validation counts against a very large table were way off. I lost some sleep fearing we had a big corruption problem despite DBCC CHECKDB and DBCC CHECKTABLE indicating no problem. Also lost time rebuiling indexes unnecessarily. Then I had to review the entire ETL to make sure no processes had caused downstream errors based on incorrect counts. It was not a happy week at work!
-Jean
March 30, 2003 at 4:15 pm
Hi Everyone,
You may recall in the SQL help file, SQL NULL is nothing, which means you cannot measure it or compare it. This means that SQL is working correctly. The way around this is to use a function in the query where clause ex. Select * from MyTable where ISNULL(MyField, '1' ) = 1 .
HTH
Andrew
March 31, 2003 at 2:31 am
Jean - thanks for that, good to know about this as I'm sure this problem will come up again in the future (at least until SP4).
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
April 1, 2003 at 6:56 am
Hi, this works - I 'forced' one of the over deveopers to test it.
Hopefully there will be a patch out soon..
dan
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply