Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 951574 - Last Review: April 24, 2008 - Revision: 1.1
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"
Bug #: 425037 (SQLBUDT)
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.
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:
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
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
APPLIES TO
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Workgroup Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
| kbexpertiseadvanced kbtshoot kbprb KB951574 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please