February 5, 2007 at 2:23 am
Hopefully someone here can help with this.
We have migrated a SQL2000 database from a 32bit server installation (that works perfectly well) via backup and restore to an installation of SQL2005 (tested on several 32bit and x64 versions) and we get the following somewhat odd behaviour around issuing updates that address a single specified row. Selects, Inserts and updates of ranges seem to work fine.
For example;
UPDATE dbo.Client
SET
Title = 'Mr'
WHERE ClientId = 2
Msg 8624, Level 16, State 116, Procedure upd_Client, Line 4
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.
However, the following works.
UPDATE cbo.Client
SET
Title = 'THE'
WHERE ClientId < 1000
(168 row(s) affected)
The above queries both have estimated execution plans that look fine?
I've tried rebuilding indexes (clustered and not) and stats to no avail. It seems to effect all tables that were in the backup but not new tables that have been created since?
Am I missing something obvious? Any ideas would be much appreciated.
February 5, 2007 at 3:03 am
This seems to be some kind of bug and you're not the first one to encouter this.
Try the folowing:
Check if the database compatibility level is 90.
Run sp_updatestats. Statistics were invalidated when you migrated to SQL 2005. Since the query optimzer relies on statistics this might cause the problem.
Markus
[font="Verdana"]Markus Bohse[/font]
February 5, 2007 at 4:09 am
Thanks Markus but still no joy?
Very infuriating this one.
February 5, 2007 at 5:47 am
I'd also try dbcc updateusage(0) in the database.
Are the collations all the same
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 5, 2007 at 5:52 am
there's also a few threads on msdn forums, some errors are fixed in sp1.
One suggestion is to change the data type of the column or add/remove nulls. Make it work and then put it back to as it was. e.g. make the title column one char wider, this should make the query work, apparently, then change it back to what it was and all should be well.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 6, 2007 at 2:39 am
Thanks for the suggestions so far. I've tried them but to no avail!
I've installed all of the hotfixes related to the error and even tried SP2 CTP.
I now have a large flat red area on my forehead from banging it on my desk!
February 6, 2007 at 2:54 am
bcp data out of table. Drop table. Create new table, bcp data back in.
This is usually my final step for a problem.
If it still fails then get on to microsoft support.
I'm glad I've not encountered this, it would drive me nuts!!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply