Filtered Index Error – Fixing ANSI_WARNINGS Settings

filtered-indexsql server

While executing proc1, I am getting error as below:

UPDATE failed because the following SET options have incorrect
settings: 'ANSI_WARNINGS'. 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 know this is happening because I am having a filtered index on customer table
and that requires SET ANSI_WARNINGS ON.

Even we need have some ANSI options which need to be taken care while
creating filtered index and stored proc. I've handled those things.

And I've fixed the issue by setting ansi_warning on in proc1 which is I think is the ideal solution.

But my question is, even after setting all required ANSI options in proc2, why I am getting this error? Any idea?

Below is my sample code:

create Customers
(
id int,
name varchar(100),
[Stats] BIT NOT NULL DEFAULT 1,
)
go

CREATE NONCLUSTERED INDEX [IX_Customers_Stats]
  ON [dbo].[Customers]([Stats])
  WHERE [Stats] = 0
go

CREATE PROC proc1
as
BEGIN
SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;

....
...
.. some code .....
....
...

EXEC proc2 @customerId

END
go


createPROCEDURE [dbo].[proc2 ]  
    @customerId INT  
AS   
BEGIN  

    SET ANSI_WARNINGS ON;
    SET ANSI_NULLS ON;
    SET ANSI_PADDING ON;
    SET ARITHABORT ON;
    SET CONCAT_NULL_YIELDS_NULL ON;
    SET NUMERIC_ROUNDABORT ON;
    SET QUOTED_IDENTIFIER ON;

    UPDATE dbo.Customers set Stats = 0 where id = @CustomerId  

END  

Best Answer

Possibly because (source):

ANSI_WARNINGS is not honored when passing parameters in a stored procedure

In any case, you can work around it in two ways:

  1. Stop setting ANSI_WARNINGS to OFF in proc1
  2. Turn ANSI_WARNINGS ON before calling proc2