Microsoft® SQL Server® 2012 Bible

519

Chapter 19: Confi guring SQL Server

19

Arithmetic Abort

The ARITHABORT connection setting can handle query termination if an arithmetic

error such as data overfl ow or divide-by-zero occurs. The default database setting for

ARITHABORT is OFF.

What exactly is terminated also depends on the ANSI_WARNINGS setting. Table 19-10

explains the behavior based on the values of ANSI_WARNINGS and ARITHABORT.

TABLE 19-10 ANSI_WARNINGS and ARITHABORT Behavior

ARITHABORT ANSI_WARNINGS Behavior

ON ON Query is aborted.

ON OFF Batch is aborted or transaction is rolled back.

OFF ON Query is aborted.

OFF OFF No warning is raised and null is returned.

To set ARITHABORT in code at connection level, do the following:

SET ARITHABORT ON;

If ARITHABORT is not specifi ed, then the settings of the current database apply. To enable

ARITHABORT for the AdventureWorks2012 sample database in code, do the following:

ALTER DATABASE AdventureWorks2012 SET ARITHABORT ON;

To enable ARITHABORT at the server level in code, do the following:

EXEC sp_configure 'user options', 64;

RECONFIGURE;

Arithmetic Ignore

The ARITHIGNORE connection setting controls whether an error message is returned

from arithmetic overfl ow or divide-by-zero errors. To abort the query, you need to use

the ARITHABORT setting. Both ARITHABORT and ARITHIGNORE can be set to ON but

ARITHABORT takes precedence over ARITHIGNORE. To set ARITHIGNORE in code, do the

following:

SET ARITHIGNORE ON;

To enable ARITHIGNORE at the server level in code, do the following:

EXEC sp_configure 'user options', 128;

RECONFIGURE;

c19.indd 519c19.indd 519 7/30/2012 5:43:10 PM7/30/2012 5:43:10 PM

http://www.it-ebooks.info