DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on

Dear all,
when I try to execute  a SP I got this error
DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'. 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 set :
SET ANSI_NULLS ON
SET ANSI_PADDING OFF
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER OFF

any ideas?
ethar1Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
When you use SQL Server Agent you have to do a SET ARITHABORT ON and SET QUOTED_IDENTIFIER ON in the job step, as in:

SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
EXEC usp_YourStoredProcedureGoesHere
0
 
Surendra NathTechnology LeadCommented:
Apparently the target table is part of indexed view. When you work with
an indexed view, the following SET options must be on: ANSI_PADDING,
ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL
and ARITHABORT.
Of these the last three depend on run-time values only.
ANSI_PADDNING also depends on how the setting when the columns were
created. And for ANSI_NULLS and QUOTED_IDENTIFIER the setting is saved
when you create with the stored procedure/trigger.

So please drop the stored procedure, set the options to ON and create the stored procedure in the same query management window and give it a try
0
 
ethar1Author Commented:
I drop the SP , and set the options , then recreate the SP , but same result.
also I save those options  saved on the SP.

How can list all indexed views?
0
 
ethar1Author Commented:
any update?
0
 
ethar1Author Commented:
I got the error only when run it from MS Agent, but when I run the SP from Microsoft SQL Server Management Studio       its work fine

Executed as user: NT AUTHORITY\SYSTEM. DELETE failed because the following SET options have incorrect settings: 'ARITHABORT'. 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. [SQLSTATE 42000] (Error 1934).  The step failed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.