Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8298
  • Last Modified:

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?
0
ethar1
Asked:
ethar1
  • 3
1 Solution
 
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
 
Anthony PerkinsCommented:
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now