Delete/insert failed with Filtered Index on SQL Server 2008?

Question

I create a stored procedure with default setting like:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Create  PROCEDURE [dbo].[TestSP] 
AS
BEGIN
   set arithabort off
   set arithignore on
   --.....
END

It is fine. Then I created a filtered unique index on a table for not null value in DB, then I can not run any stored procedure anymore in SQL Server Management Studio. The error I get is something like:

DELETE(or 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.

I try to run the stored procedure like:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
EXEC TestSP

Or

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
EXEC TestSP

I still get above error. I'm very confused. How to resolve this problem?

Answer 1

The settings for both ANSI_NULLS and QUOTED_IDENTIFIER are based on what those settings are when you create/alter a procedure, not the settings in the instance you run the procedure in.

You want to run an ALTER PROC for your TestSP that sets at least QUOTED_IDENTIFIER to ON when you run it.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TestSP] 

AS
BEGIN
   set arithabort off
   set arithignore on
   --.....
END