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