SQL Server 2005 9.0.3161 on Win 2k3 R2
I receive the following error:
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
VALUES (@.journal_id, @.acct_id, @.acct_recv_id, @.amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
I'd suggest calling PSS. It does sound like a bug here.
|||I ran into this exact problem today, and it turned out to be related to a catalog being inconsistent, when we performed a migration from a SQL 2000 server to a SQL 2005 server, by restoring a backup . There was a foreign key constraint that was not functioning correctly following the migration and that was causing the error to show up.
We had restored the SQL 2000 backup to a 2005 server, then changed the compatability mode from 80 to 90, then updated statistics. We did not however, run the DBCC following all of that. We had run tests prior to the migration, but this hadn't showed up as an issue until the day of the migration. As a matter of fact, we had run DBCC's on the SQL 2000 database and things were fine. We checked the backup file, and that was fine. The problem was in some part of the page migrations that happen within the engine itself. Maybe this was a page alignment issue, whereby a given catalog page was in a decent state for migration when we were testing, but changed it's alignment on a given page between the time we tested and the time that we migrated. Who know....that's just my speculation.
The error from running a simple insert statement looks like this:
Msg 8624, Level 16, State 1, Line 1
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.
The error from the DBCC CHECKDB looks like this:
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=238623893,key_index_id=3) of row (object_id=951674438) in sys.foreign_keys does not have a matching row (object_id=238623893,index_id=3) in sys.indexes.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
This lead us to the conclusion that we could drop the FK and recreate it, and have everything work. And, it did.
The moral of the story, like it's been said many times...run DBCC's after restores when going from SQL 2000 to SQL 2005.
Hope this helps someone else in the future.
-- Don
|||
I consulted tech support. It was finally classified as a bug. The database was in 80 compatibility and changing it to 90 made the problem go away. The other work around was to set arithabort on. This explained why it worked from management studio and not anywhere else. Apparently management studio has arithabort set to on by default. The following is the case closure confirmation from the MS engineer:
PROBLEM:
=======
An insert query to a table that has foreign key references cannot generate a plan with error 8624 when arithabort is set to be off.
Server: Msg 8624, Level 16, State 1, Line 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.
AGREED UPON CRITERIA FOR RESOLUTION:
===========================
Investigate root cause
CAUSE:
=====
This issue appears to be related to the fact database compatibility is set to 80.I have filed a product bug for tracking purpose
RESOLUTION:
===========
Setting database compatibility to be 90 resolved the issue
