SQL Server: Incorrect SET options on a stored procedure error
We had to put out a another fire at work when a Stored Procedure that was not modified in ages started to fail. Following is the error that was caught by the application.
INSERT failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
As specified in the error above, there was something wrong with the SET options. After a little bit of research I figured out that if the SET options are not correctly defined, this error could occur. Especially when a filtered index is added to a table, sql server requires it to be created with SET QUOTED_IDENTIFIER setting as ON. Take a look at the following blog post to recreate this error.
First attempt at fixing the error by SET ing the correct options on the stored procedure did not help the cause:
SET NUMERIC_ROUNDABORT OFF GO SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON GO ALTER procedure [dbo].[STORED PROCEDURE NAME]
Solution: Apparently a new index was added to a table, was causing the issue. This index was interfering with a row insert on this table. Disabling the filtered index fixed the issue. This to me seems like a temporary solution, we still have to figure out how to make the filtered index work for this table.