5

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  
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.