SQL Server - INSERT failed because of 'ARITHABORT'

Question

I use NHibernate and SQL Server 2005 and I have an index on a computed column in one of my tables.

My problem is that when I insert a record to that table I get the following error:

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

I use SET ARITHABORT ON; before my inserts but still have this error.

Answer 1

Open SQL Server Mgt Studio, right click on the server name, choose Properties, open Connection Tab and then check the arithmetic abort option

Answer 2

For inserts on tables with computed columns, you need these set options:

The NUMERIC_ROUNDABORT option must be set to OFF, and the following options must be set to ON:

ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER

Try adding this before your insert:

set NUMERIC_ROUNDABORT off
set ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER on

insert ...

Answer 3

I ran into this problem today and got it solved.

I created a filtered index on a column in sql server 2008 r2, but would get this error when inserting.

I saw that this question wasn't completely answered since it might be tedious to always SET ARITHABORT ON in every insert.

I found a blog that showed it was the database compatibility level that was the issue. I changed the compatibility level from 80 (2000) to 100 (2008) and the issue was solved. Not sure if changing the compatibility level to 90 would solve this or not, but worth a try.

SQL Server compatibility levels and command to change here. http://msdn.microsoft.com/en-us/library/bb510680.aspx

If this doesn't work or you can't change the compatibility mode, there is another workaround by adding a trigger in the blog I was talking about here http://chrismay.org/2013/05/23/interesting-problem-with-sql-server-arithabort-filtered-indexes-calculated-columns-and-compatibility-mode-of-80/

Note that this change was done on a test database, and all applications should be tested before making a change like this in production. Make sure this is ok with your DBA before changing too.