SQL Server - INSERT failed because of 'ARITHABORT'

sql-server sql-server-2005 calculated-columns database-indexes
S.M.Amin picture S.M.Amin · Feb 19, 2013 · Viewed 17.4k times · Source

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

muhmud picture muhmud · Feb 19, 2013

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

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More