Archive for the ‘Filtered Indexes’ Category

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