Filtered index throwing error - UPDATE failed because the following SET options have incorrect settings: ANSI_WARNINGS

Question

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  

Answer 1

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

Answer 2

In my case it was caused by a filtered index

CREATE UNIQUE NONCLUSTERED INDEX IX_PAYMENTS_apiKey
ON dbo.PAYMENTS(apiKey)
WHERE apiKey IS NOT NULL;

Solution was to change ANSI_WARNINGS OFF for LEFT(column, length).