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:
INSERT failed because the following SET options have incorrect settings: ‘ARITHABORT’
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