First things first: I'm using MS SQL Server 2008 with a database at compatibility level 80, and connecting to it with .Net's System.Data.SqlClient.SqlConnection.

For performance reasons I've created an indexed view. As a result, updates to tables referenced in the view need to be done with ARITHABORT ON. However, the profiler shows that SqlClient is connecting with ARITHABORT OFF, so updates to those tables are failing.

Is there a central configuration setting to make SqlClient use ARITHABORT ON? The best I've been able to find is to manually execute that each time a connection is opened, but updating the existing code base to do this would be a fairly large task so I'm keen to find a better way.


6 Answers 6


Seemingly preferred approach

I was under the impression that the following had been tested already by others, especially based on some of the comments. But my testing shows that these two methods do indeed work at the DB level, even when connecting via .NET SqlClient. These have been tested and verified by others.


You can set the user options server configuration setting to be whatever it is currently bit-wise ORed with 64 (the value for ARITHABORT). If you do not use the bit-wise OR (|) but instead do a straight assignment (=) then you will wipe out any other existing options already enabled.


SELECT @Value = CONVERT(INT, [value_in_use]) --[config_value] | 64
FROM   sys.configurations sc
WHERE  sc.[name] = N'user options';

IF ((@Value & 64) <> 64)
  PRINT 'Enabling ARITHABORT...';
  SET @Value = (@Value | 64);

  EXEC sp_configure N'user options', @Value;

EXEC sp_configure N'user options'; -- verify current state


This can be set per-database via ALTER DATABASE SET:

USE [master];

     SELECT *
     FROM   sys.databases db
     WHERE  db.[name] = N'{database_name}'
     AND    db.[is_arithabort_on] = 0
  PRINT 'Enabling ARITHABORT...';


Alternate approaches

The not-so-good news is that I have done a lot of searching on this topic, only to find that over the years a lot of others have done a lot of searching on this topic, and there is no way to configure the behavior of SqlClient. Some MSDN documentation implies that it can be done via a ConnectionString, but there are no Keywords that would allow for altering these settings. Another document implies it can be changed via Client Network Configuration / Configuration Manager, but that does not seem possible either. Hence, and rather unfortunately, you will need to execute SET ARITHABORT ON; manually. Here are some ways to consider:

IF you are using Entity Framework 6 (or newer), you can try either:

  • Use Database.ExecuteSqlCommand: context.Database.ExecuteSqlCommand("SET ARITHABORT ON;");
    Ideally this would be executed once, after opening the DB connection, and not per each query.

  • Create an interceptor via either:

    This will allow you to modify the SQL before it is executed, in which case you can simply prefix it with: SET ARITHABORT ON;. The downside here is that it will be per each query, unless you store a local variable to capture the state of whether or not it has been executed and test for that each time (which really isn't that much extra work, but using ExecuteSqlCommand is probably easier).

Either of those will allow you to handle this in one spot without changing any existing code.

ELSE, you could create a wrapper method that does this, similar to:

public static SqlDataReader ExecuteReaderWithSetting(SqlCommand CommandToExec)
  CommandToExec.CommandText = "SET ARITHABORT ON;\n" + CommandToExec.CommandText;

  return CommandToExec.ExecuteReader();

and then just change the current _Reader = _Command.ExecuteReader(); references to be _Reader = ExecuteReaderWithSetting(_Command);.

Doing this also allows for the setting to be handled in a single location while requiring only minimal and simplistic code changes that can be mostly done via Find & Replace.

Better yet (Else Part 2), since this is a connection level setting, it doesn't need to be executed per each SqlCommand.Execute__() call. So instead of creating a wrapper for ExecuteReader(), create a wrapper for Connection.Open():

public static void OpenAndSetArithAbort(SqlConnection MyConnection)
  using (SqlCommand _Command = MyConnection.CreateCommand())
    _Command.CommandType = CommandType.Text;
    _Command.CommandText = "SET ARITHABORT ON;";




And then just replace the existing _Connection.Open(); references to be OpenAndSetArithAbort(_Connection);.

Both of the above ideas can be implemented in more OO style by creating a Class that extends either SqlCommand or SqlConnection.

Or Better yet (Else Part 3), you can create an event handler for the Connection StateChange and have it set the property when the connection changes from Closed to Open as follows:

protected static void OnStateChange(object sender, StateChangeEventArgs args)
    if (args.OriginalState == ConnectionState.Closed
        && args.CurrentState == ConnectionState.Open)
        using (SqlCommand _Command = ((SqlConnection)sender).CreateCommand())
            _Command.CommandType = CommandType.Text;
            _Command.CommandText = "SET ARITHABORT ON;";


With that in place, you only need to add the following to each place where you create a SqlConnection instance:

_Connection.StateChange += new StateChangeEventHandler(OnStateChange);

No changes to existing code are needed. I have just tried this method in a small console app, testing by printing the result of SELECT SESSIONPROPERTY('ARITHABORT');. It returns 1, but if I disable the Event Handler, it returns 0.

For the sake of completeness, here are some things that do not work (either at all or not as effectively):

  • Logon Triggers: Triggers, even while running in the same session, and even if running within an explicitly started transaction, is still a sub-process and hence its settings (SET commands, local temporary tables, etc) are local to it and do not survive the end of that sub-process.
  • Adding SET ARITHABORT ON; to the beginning of each stored procedure:
    • this requires a lot of work for existing projects, especially as the number of stored procedures increases
    • this does not help ad hoc queries
  • I just tested creating a simple database with ARITHABORT and ANSI_WARNINGS both off, created a table with zero in it, and a simple .net client to read from it. The .net SqlClient showed as setting ARITHABORT off and ANSI_WARNINGS on in the login in sql profiler, and also failed the query with a divide by zero as expected. This seems to show that the preferred solution of setting db level flags will NOT work for changing the default for .net SqlClient.
    – Mike
    Commented Jul 23, 2019 at 1:18
  • can confirm that setting server-wide user_options DOES work however.
    – Mike
    Commented Jul 23, 2019 at 1:47
  • I'm also observing that with SELECT DATABASEPROPERTYEX('{database_name}', 'IsArithmeticAbortEnabled'); returning 1, sys.dm_exec_sessions shows arithabort off, though I don't see any explicit SETs in Profiler. Why would this be? Commented Aug 27, 2019 at 19:50
  • SET ARITHABORT ON did not work from c# but worked from SP(2008 R2).
    – codemirror
    Commented Mar 20, 2020 at 20:42
  • As for the Part 3, I don't think an event handler would ever give you any warranty on executing before subsequent queries, does it? I'm going to use it thou...
    – Shockwaver
    Commented Jun 4, 2020 at 8:24

Option 1

Aside from Sankar's solution, setting the arithmetic abort setting at the server level for all connections will work:

EXEC sys.sp_configure N'user options', N'64'

As of SQL 2014 it's recommended to be on for all connections:

You should always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.

So this would seem to be the ideal solution.

Option 2

If option 1 is not viable and you use stored procedures for most of your SQL calls (which you should, see Stored Procedures vs. Inline SQL) then simply enable the option in each relevant stored procedure:

   SELECT ...

I believe the best real solution here is to simply edit your code, as it's wrong and any other fix is merely a workaround.

  • I do not think it helps setting it on for the SQL Server, when the .net connection starts off with set ArithAbort off. I was hoping for something that could be done on the .net / C# side. I put up the bounty, because I'd seen the recommendation. Commented Mar 10, 2015 at 6:51
  • 1
    The .net/C# side is what Sankar covered, so these are pretty much the only options. Commented Mar 10, 2015 at 13:08
  • I tried Option 1 and it had no effect. New sessions still show as having arithabort = 0. I'm not having any issues from it, just trying to get ahead of potential problems. Commented Mar 31, 2017 at 20:17

I am NOT an expert here but you can try something like below.

String sConnectionstring;
sConnectionstring = "Initial Catalog=Pubs;Integrated Security=true;Data Source=DCC2516";

SqlConnection Conn = new SqlConnection(sConnectionstring);

SqlCommand blah = new SqlCommand("SET ARITHABORT ON", Conn);

SqlCommand cmd = new SqlCommand();
// Int32 rowsAffected;

cmd.CommandText = "dbo.xmltext_import";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = Conn;
//Console.Write ("Connection is open");
//rowsAffected = 

Ref: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d9e3e8ba-4948-4419-bb6b-dd5208bd7547/

  • Yes, that's what I meant by manually executing it. The thing is that the codebase I'm working with has accrued quite a bit of technical debt when it comes to the DB access layer, so I'll have to refactor a few hundred methods to do it this way. Commented May 3, 2011 at 19:45

There is no setting to force SqlClient to always set ARITHABORT on, you have to set this as you describe.

Interestingly from the Microsoft documentation for SET ARITHABORT: -

You should always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.

And yet the .Net connection is hard-coded to set this off by default?

As another point, you have to be very careful when diagnosing performance issues with this setting. Different set options will result in different query plans for the same query. Your .Net code could experience a performance issue (SET ARITHABORT OFF) and yet when you run the same TSQL query in SSMS (SET ARITHABORT ON by default) it could be fine. This is because the .Net query plan will not be reused and a new plan generated. This could potentially eliminate a parameter sniffing problem for example and give much better performance.

  • 1
    @HenrikStaunPoulsen - Unless you are stuck using 2000 (or 2000 compatibility level) it doesn't make any difference though. It is implied by ANSI_WARNINGS on in later versions and things like indexed views work fine. Commented Mar 14, 2015 at 21:12
  • 1
    Note that .Net is not hard-coded to set ARITHABORT off. SSMS defaults to setting it on. .Net is just connecting and using the server/database defaults. You can find issues on MS Connect about users complaining about SSMS's default behavior. Note the warning on the ARITHABORT doc page.
    – Bacon Bits
    Commented Oct 20, 2017 at 13:14

If it saves anyone some time, in my case (Entity Framework Core 2.0.3, ASP.Net Core API, SQL Server 2008 R2):

  1. There are no Interceptors on EF Core 2.0 (I think they will be available soon on 2.1)
  2. Neither changing the global DB setting nor setting the user_options was acceptable for me (they DO work - I tested) but I couldn't risk impacting other applications.

An ad-hoc query from EF Core, with SET ARITHABORT ON; at the top, does NOT work.

Finally, the solution that worked for me was: Combining a stored procedure, called as a raw query with the SET option before EXEC separated by a semicolon, like this:

// C# EF Core
int result = _context.Database.ExecuteSqlCommand($@"
EXEC MyUpdateTableStoredProc
             @Param1 = {value1}
  • Interesting. Thanks for posting these nuances of working with EF Core. Just curious: is what you are doing here essentially the wrapper option I mentioned in the ELSE sub-section of the Alternate approaches section in my answer? I was just wondering because you mentioned the other suggestions in my answer either not working or not viable due to other constraints, but didn't mention the wrapper option. Commented Feb 28, 2018 at 20:43
  • @SolomonRutzky it is equivalent to that option, with the nuance that it is limited to executing a stored procedure. In my case if I prefix a raw update query with the SET OPTION (manually or via the wrapper) it doesn't work. If I put the SET OPTION inside the stored procedure, it doesn't work. The only way was to do SET OPTION followed by the EXEC stored procedure in the same batch. Which is way I opted to customized the specific call instead of making the wrapper. Soon we'll update to SQLServer 2016 and I can clean this up.Thanks for your answer, if was helpful to discard specific scenarios. Commented Mar 2, 2018 at 5:06

Building on Solomon Rutzy answer, for EF6:

using System.Data;
using System.Data.Common;

namespace project.Data.Models
    abstract class ProjectDBContextBase: DbContext
        internal ProjectDBContextBase(string nameOrConnectionString) : base(nameOrConnectionString)
            this.Database.Connection.StateChange += new StateChangeEventHandler(OnStateChange);

        protected static void OnStateChange(object sender, StateChangeEventArgs args)
            if (args.OriginalState == ConnectionState.Closed
                && args.CurrentState == ConnectionState.Open)
                using (DbCommand _Command = ((DbConnection)sender).CreateCommand())
                    _Command.CommandType = CommandType.Text;
                    _Command.CommandText = "SET ARITHABORT ON;";

        protected override void OnModelCreating(DbModelBuilder modelBuilder)

This uses System.Data.Common's DbCommand instead of SqlCommand, and DbConnection instead of SqlConnection.

A SQL Profiler trace confirms, SET ARITHABORT ON is sent when the connection opens, before any other commands are executed in the transaction.

  • I don't think an event handler would ever give you any warranty on executing before subsequent queries, does it? I'm going to use it thou...
    – Shockwaver
    Commented Jun 4, 2020 at 8:26
  • @Shockwaver, If the connection is still open on subsequent commands, SET ARITHABORT remains ON. If the connection is closed and then reopened, the event will fire again, before any other command is executed. I hope I understood your question.
    – CapNCook
    Commented Jun 5, 2020 at 14:12
  • I meant something different. If you open the connection and then send a query I think you have no warranty the event handler (with the ARITHABORT query in it) will be executed BEFORE the other query. It dependes on how the handler is called, I didn't dig through EF code thou, I could be wrong
    – Shockwaver
    Commented Jun 10, 2020 at 10:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.