SQL Server – INSERT failed because of 'ARITHABORT'

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:

  • Is there a way to find out what a stored procedure returns without running it?
  • SQL Server insert performance
  • How to select a column in SQL Server with a special character in the column name?
  • Entity Framework sending parameters as unicode causing performance problems
  • Visual Studio SSDT Data Compare how to compare two tables in a single database
  • Re-ordering rows based on position SQL Server
  • INSERT failed because the following SET options have incorrect settings: ‘ARITHABORT’

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

    5 Solutions collect form web for “SQL Server – INSERT failed because of 'ARITHABORT'”

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

    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. Saw this question wasn’t completely answered since it might be tedious to always turn arithabort on in every insert. I found a blog that showed it was the database compatibility level that was the issue. 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.

    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.

    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;
    

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.