Filtered Indexes and Quoted_Identifier

Posted: April 11, 2012 in Design, Filtered Indexes, Indexing

I recently added a filtered index to a table without any problems. Later that night jobs began to fail with the error:

Description: Executing the query “NameOfStoredProcedure” failed with the following error: “UNKNOWN TOKEN failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and…

The problem was with the Quoted Identifier settings for the stored procedure. For the query to work the Quoted Identifier needs to be set to ON. The problem is that each stored procedure inherits the settings used when it was created.

You can check these settings by right-clicking on the stored proc and selecting properties. In the general tab there is a list of options which includes the QUOTED_IDENTIFIER option. These values will be set to either True or False.

The default Value for QUOTED_IDENTIFIER is ON.

From BOL

QUOTED_IDENTIFIER

When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.

http://msdn.microsoft.com/en-us/library/ms174393.aspx

Create Procedure

The Database Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQL procedure is created or modified. These original settings are used when the procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored when the procedure is running.

Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a procedure is created or modified. If the logic of the procedure depends on a particular setting, include a SET statement at the start of the procedure to guarantee the appropriate setting. When a SET statement is executed from a procedure, the setting remains in effect only until the procedure has finished running. The setting is then restored to the value the procedure had when it was called. This enables individual clients to set the options they want without affecting the logic of the procedure.

Any SET statement can be specified inside a procedure, except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL. These must be the only statements in the batch. The SET option chosen remains in effect during the execution of the procedure and then reverts to its former setting.

http://msdn.microsoft.com/en-us/library/ms187926.aspx

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