INSERT or UPDATE or DELETE failed because the following SET options have incorrect settings: 'ARITHABORT' or something else
Quick fix: look for indexed views or indexes on tables with computed columns and delete the index or change it to not reference the view or the computed column.
I got this error while trying to optimize an SQL server. I did the trace, I used the Database Engine Tuning Advisor, it gave me an sql file of magical Create Statistics and Create Index lines. But after I applied it, happy that I will get a 38% increase in my SQL speed, I got this weird error:
UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'
I wasn't aware that a cluster could break your database! So I found this little Microsoft article: PRB: Error "INSERT Failed" When You Update Table Referenced in an Indexed View.
Yet it only speaks about Indexed Views and it somehow shifts the blame to some missing ArithAbort setting. Not so. In my case it was about one of the indexes referencing a computed column in the INCLUDE statement.
In my particular case, changing the offending index to not reference the computed column was the solution. Of course, indexing computed columns and views is totally possibly, but it depends on how you create those indexes. In my case, SET ARITHABORT ON was set before creating the index. The solution in the Microsoft Support article might be better, even if less attractive to lazy people as myself.
I got this error while trying to optimize an SQL server. I did the trace, I used the Database Engine Tuning Advisor, it gave me an sql file of magical Create Statistics and Create Index lines. But after I applied it, happy that I will get a 38% increase in my SQL speed, I got this weird error:
UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'
I wasn't aware that a cluster could break your database! So I found this little Microsoft article: PRB: Error "INSERT Failed" When You Update Table Referenced in an Indexed View.
Yet it only speaks about Indexed Views and it somehow shifts the blame to some missing ArithAbort setting. Not so. In my case it was about one of the indexes referencing a computed column in the INCLUDE statement.
In my particular case, changing the offending index to not reference the computed column was the solution. Of course, indexing computed columns and views is totally possibly, but it depends on how you create those indexes. In my case, SET ARITHABORT ON was set before creating the index. The solution in the Microsoft Support article might be better, even if less attractive to lazy people as myself.
Comments
Be the first to post a comment