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: 343
  • Last Modified:

sql server alter database question

I am trying to delete a row in my db and I get this message:

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.

I have done a little research and it appears that I need to run the following command:

ALTER DATABASE MyReportingDB SET ARITHABORT ON

Is that so and, if so, why?

Thanks~
0
Bob Schneider
Asked:
Bob Schneider
  • 3
  • 3
4 Solutions
 
sumerdaiCommented:
How are you trying to delete it? Are using the SQL management tools or another tool? I think that when you connect through other tools ARITHABORT is OFF by default.
0
 
Eugene ZCommented:
please check if it is your case
Incorrect requirement that ARITHABORT should be ON for XQuery and XML methods
http://connect.microsoft.com/SQLServer/feedback/details/354563/incorrect-requirement-that-arithabort-should-be-on-for-xquery-and-xml-methods

please tell more: is it xml data, indexed view part?
what your db the compatibility level is 90 or 100
if it is 80 try to change to 100 (sql2008) and try delete code
0
 
Bob SchneiderCo-OwnerAuthor Commented:
I am trying to delete a single record in my sql server 2008 db.  I tried this in the "New Query" window using a "delete from mydb where ...".  It is tied to other records in other tables via a PK-FK relationship with the PK being in the record I am trying to delete.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
sumerdaiCommented:
Do either of these table have an index which is based on a computed column? If so, please try increasing your compatibility level  (as EugeneZ suggested) by right clicking on the database and selecting properties.  You can find the compatibility Level on the Options page. Alternately, you can change the index to not reference the computed column.

If this does not seem to be the case, please check if either of these tables is used in an indexed view, as this will cause the same issue.
0
 
Bob SchneiderCo-OwnerAuthor Commented:
the PK table, the one that houses the record I am deleting, is used in an index.  
0
 
sumerdaiCommented:
Then you either must raise the compatibility level or remove the table from the index
0
 
Bob SchneiderCo-OwnerAuthor Commented:
I raised the compatibility level to 100.  We will see what happens.  Thanks!!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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