Error message when you update a table column that is a foreign key in SQL Server 2005: "Internal Query Processor Error: The query processor could not produce a query plan"

View products that this article applies to.

Bug #: 425037 (SQLBUDT)

SYMPTOMS

You have a table in Microsoft SQL Server 2005. The table contains a computed column. You specify the PERSISTED keyword on the computed column. The table also has a foreign key constraint that references another table. Additionally, you set the NUMERIC_ROUNDABORT option to ON, or you set one or more of the following options to OFF:

ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
QUOTED_IDENTIFIER
CONCAT_NULL_YIELDS_NULL

When you run an UPDATE statement to update the table column that is the foreign key, you receive the following error message:

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.

Back to the top

WORKAROUND

To work around this problem, set the NUMERIC_ROUNDABORT option to OFF if it is set to ON. You must also set the following options to ON if they are set to OFF:

ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
QUOTED_IDENTIFIER
CONCAT_NULL_YIELDS_NULL

To set the NUMERIC_ROUNDABORT option to OFF, run the following statement:

SET NUMERIC_ROUNDABORT OFF
GO

To set an option to ON, run the following statement:

SET OptionName ON
GO

Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Back to the top

MORE INFORMATION

Steps to reproduce the problem

Run the following statement in SQL Server Management Studio:

SET ANSI_NULLS ON
GO
CREATE TABLE Table1 (Col1 INT PRIMARY KEY, Col2 AS Col1+1 PERSISTED)
GO
CREATE TABLE Table2 (Col1 INT REFERENCES Table1 (Col1))
GO 
UPDATE Table2 SET Col1 = Col1 + 1  --The statement finishes successfully.
GO
SET ANSI_NULLS OFF
GO
UPDATE Table2 SET Col1 = Col1 + 1  --The statement generates an error message.
GO

Back to the top