Correcting: SELECT failed because the following SET options have incorrect settings: ‘ARITHABORT’

This error showed up on a stored procedure that I was trying to execute from an ASP.NET Web page. The strange part was that I could execute the stored procedure directly from SSMS without any problems.

The procedure was very simple in its design, but the database I was working in was new to me and I had very limited access. The procedure was set up to receive an XML parameter and then to do a bulk insert into a table from the XML.

Here is the full error text:

INSERT 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.

As you can see, this error message is quite generic and is of almost no help at all in figuring out what is going wrong. The fact that the procedure executes from SSMS but not via an ASP.NET makes it all the more mysterious.

Sources online seemed to indicate that this error might be happening because of computed columns in the table that I was trying to insert into. However, this was not the case: the table had no computed columns. In general there was a lot of confusion about this error, likely stemming from multiple possible problem sources.

The primary problem for me was that the database was one that I have very limited access to and could not check or adjust its settings.

The Solution:

I was finally able to fix this error by placing the following commands above my INSERT statement:

SET NUMERIC_ROUNDABORT OFF

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON

I found this solution on StackOverflow at: http://stackoverflow.com/questions/14953450/sql-server-insert-failed-because-of-arithabort .

It’s nice when a solution works, but I’m still not quite certain what caused the error to happen in the first place. It’s difficult to properly debug and correct a problem when the error messages themselves are so open-ended and vague.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s