Wednesday, February 14, 2007

Sudden SQL error during INSERT/UPDATE operation (ARITHABORT)

Your database has been running fine for the longest time. All of a sudden INSERT or UPDATE operations fail with an error similar to the following.

UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'

Quick research tells you to simply add the following line right after you open a connection to the database or at the start of your query.


This seems to make things work again, but have you really solved the problem?

It turns out that this error is caused when you create an Indexed View. But I didn't do it? Well, if you run either the Index Tuning Wizard (SQL Server 2000) or Database Engine Tuning Advisor (SQL Server 2005), they will create what are called "Hypothetical Materialized Views" or index views. These views start with the prefix _hypmv. When the analysis is finished these views are supposed to be discarded. However, if for whatever reason these views are not deleted, any attempts to INSERT or UPDATE into the table(s) on which this indexed views were created, the error message relating to incorrect ARITHABORT setting will occur.

In my particular case, the reason why these views were not discarded was because my account had enough privileges to create these views but not delete them. Running the tuning tools would leave these indexed views behind without me realizing it.

So, if you see this error it may be that you have left behind some indexed views you did not create directly. Deleting them should get rid of the error.

1 comment:

Stephen K said...

And 4 years later you save my life. Thank you for this article.