SQL Server “Could Not Produce a Query Plan” and “SET QUOTED_IDENTIFIER”
December 21, 2012 Leave a comment
On my latest project we recently ran into a situation where execution of a particular stored procedure was producing the error “Internal Query Processor Error: The query processor could not produce a query plan.”
Investigation into this problem uncovered a number of references to foreign keys and the SET ANSI_NULLS option. A few sources suggested that computed columns contributed to the problem. There were also hints of bugs in certain versions of SQL Server which may or may not have been corrected in subsequent service packs.
In our case, we were up-to-date on service packs and updates. Modifying the state of the SET ANSI_NULLS option had no affect, we were able to rule out issues with foreign keys, and the tables in question did not have any computed columns. Further investigation suggested the SET QUOTED_IDENTIFIER option might also trigger the problem, and that turned out to be the case for us. Setting that option to ON for the stored procedure eliminated the error.
So, based on my experience, I suggest checking the following if you encounter the “Could not produce a query plan” error when executing a stored procedure:
- Make sure you are up-to-date with service packs and patches.
- Include SET ANSI_NULLS ON in the procedure definition.
- Include SET QUOTED_IDENTIFIER ON in the procedure.
- Try eliminating foreign keys and/or computed columns and see if the error goes away.
Be aware that this problem seems to be a difficult one to pin down, and there’s no guarantee that the steps outlined here will solve your problem.