INF: Behavior if Both ARITHABORT & ARITHIGNORE Are Set ON

ID: Q143337

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5
Microsoft Open Database Connectivity, version 2.5x

SUMMARY

When you use version 2.50.0121 or later of the Microsoft SQL Server ODBC Driver, if both the ARITHABORT and ARITHIGNORE query-processing options are set ON, ARITHABORT takes precedence.

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. The options are two distinct options, so if an application does:


  SET ARITHABORT ON
  SET ARITHIGNORE ON
  GO

Then both options will be set on. When both options are set on, ARITHABORT takes precedence over ARITHIGNORE.

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

Procedures can do the following to ensure that ARITHIGNORE will always be active regardless of the client setting for ARITHABORT:


   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 will go back to what it was before the procedure was executed. With the sample code above, the ARITHABORT option is turned off for the life of the procedure so that the ARITHIGNORE option will be active, then it resets to whatever setting the client application wants when the procedure completes.

Additional query words: sql6 winnt odbc

Keywords : kbinterop kbprg SSrvProg SSrvTran_SQL
Version : WINDOWS:2.5x; winnt:6.0,6.5
Platform : WINDOWS winnt
Issue type :