Make SqlClient default to ARITHABORT ON

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.

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.

Server-wide

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.

DECLARE @Value INT;

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

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

EXEC sp_configure N'user options', @Value;
RECONFIGURE;
END;

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


Database-level

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

USE [master];

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

ALTER DATABASE [{database_name}] SET ARITHABORT ON WITH NO_WAIT;
END;


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:

• DbConfiguration.AddInterceptor

• DbInterception.Add

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;";

    MyConnection.Open();

    _Command.ExecuteNonQuery();
  }

  return;
}

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;";

            _Command.ExecuteNonQuery();
        }
    }
}

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

StackExchange Database Administrators Q#2500, answer score: 37