SQL Server “Could Not Produce a Query Plan” and “SET QUOTED_IDENTIFIER”

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:

  1. Make sure you are up-to-date with service packs and patches.
  2. Include SET ANSI_NULLS ON in the procedure definition.
  3. Include SET QUOTED_IDENTIFIER ON in the procedure.
  4. 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: