Queries failing because of the ARITHABORT setting

So here is the scenario: you have a stored procedure which inserts new rows to a database table and you want to call it from your .NET code (in my case the application was a BizTalk orchestration using WCF-SQL adapters). You can successfully execute the procedure in SQL Server Management Studio, but when your code calls it, you get an error like this:

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

 

The exception is pretty clear, you have ARITHABORT set to 'OFF' but you are trying to insert a row into a table that is referenced in an indexed view, or performing some other operation that requires the ARITHABORT setting to be 'ON'. Reason why the stored procedure works in SSMS is simply because it uses 'ON' as a default setting for ARITHABORT while OLE DB connection provider doesn't. You can find more information about the ARITHABORT setting from here: https://technet.microsoft.com/en-us/library/ms190306.aspx

 

You can also use the SQL Server Profiler to find out what statements get executed before your stored procedure is actually called. In WCF-SQL adapter's case the ARITHABORT is explicitly set to 'OFF':

 -- network protocol: TCP/IP
 set quoted_identifier on
 set arithabort off
 set numeric_roundabort off
 set ansi_warnings on
 set ansi_padding on
 set ansi_nulls on
 set concat_null_yields_null on
 set cursor_close_on_commit off
 set implicit_transactions off
 set language us_english
 set dateformat mdy
 set datefirst 7
 set transaction isolation level serializable

 

Resolving the error is pretty easy, you add the following line after the BEGIN clause in your stored procedure:

 SET ARITHABORT ON; 

 

You can also modify the setting on the database level by right-clicking your database in SSMS and selecting Properties > Options. In the list under the Miscellaneous-section you find the 'Arithmetic Abort Enabled'-setting: