SQL Server - INSERT failed because of 'ARITHAB

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

You can change the database options, so the SET options are globally available

    ALTER DATABASE [$(DatabaseName)]
        SET ANSI_NULLS OFF,
            ANSI_PADDING OFF,
            ANSI_WARNINGS OFF,
            ARITHABORT OFF,
            CONCAT_NULL_YIELDS_NULL OFF,
            NUMERIC_ROUNDABORT OFF,
            QUOTED_IDENTIFIER OFF 
        WITH ROLLBACK IMMEDIATE;

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

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

Answer 4

I also encountered this error while executing a stored procedure (that updates records) from an application and this solved my problem: https://support.microsoft.com/en-us/kb/305333

So basically, I added this set of codes before executing my stored procedure

sqlConn = New SqlConnection(connectionString)
If sqlConn.State = ConnectionState.Closed Then sqlConn.Open()

sqlCmd = New SqlCommand()
With sqlCmd
   .Connection = sqlConn
   .CommandType = CommandType.text
   .CommandText = "SET ARITHABORT ON"
   .ExecuteNonQuery()

   .CommandType = CommandType.StoredProcedure
   .CommandText = "MY PROCEDURE"
   .ExecuteNonQuery()
End With

Hope this helps someone.

Answer 5

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.