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