INF: Behavior if Both ARITHABORT & ARITHIGNORE Are Set ON (143337)

MORE INFORMATION

Some users assume that the ARITHABORT and ARITHIGNORE options are mutually exclusive and that setting one ON will automatically set the other OFF. These options are two distinct options. Therefore, if an application runs the following code

  SET ARITHABORT ON
  SET ARITHIGNORE ON
  GO
				

both options are set ON. When both options are set ON, ARITHABORT takes precedence over ARITHIGNORE.

Stored procedures that issue a SET ARITHIGNORE ON statement still have clients exhibit the SET ARITHABORT ON behavior if the client had set ARITHABORT on before calling the stored procedure. This is always true for clients that connect with the SQL Server ODBC driver, because the driver itself issues a SET ARITHABORT ON statement to ensure that its behavior complies with the ODBC SQL standard.

Procedures can perform the following to ensure that the ARITHIGNORE option is always set ON regardless of the client setting for the ARITHABORT option:

   CREATE PROCEDURE example AS
   SET ARITHABORT OFF
   SET ARITHIGNORE ON
   ... commands making up the stored procedure ...
   GO
				

Whenever a SET statement is executed in a stored procedure, the new setting is only active until the procedure completes. When the procedure completes, the connection's setting for that option returns to what it was before the procedure was executed. With the sample code above, the ARITHABORT option is turned off for the duration of the procedure so that the ARITHIGNORE option is active, and it resets to the client application's desired setting when the procedure completes.