FIX: SET ARITHABORT ON Inadvertently Causes an Insert to Fail

• Microsoft SQL Server, version 6.0

SYMPTOMS

You can use the ARITHABORT option to terminate a query when an overflow or divide-by-zero error occurs during query execution. With the new decimal and numeric datatypes, the ARITHABORT does not allow you to abort the loss of the most significant digit and ignores the loss of the least significant digit. Thus, the ARITHABORT option may incorrectly raise a message 8115:

   Arithmetic overflow error converting numeric to type numeric.

WORKAROUND

Expanding the storage, and subsequently the precision and scale, for numeric addresses this problem. In the below script, changing numeric (precision,scale) to (6,3) avoids the incorrect ARITHABORT message.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.0. This problem was corrected in Service Pack 1 for SQL Server version 6.0. For more information, contact your primary support provider.

MORE INFORMATION

For example:

   create table xxx (a int, b numeric(5,2))
   go
   -- This insert works fine --
   insert into xxx values (9999, 123.951)
   go
   set arithabort on
   go
   -- Same insert fails with Msg 8115 --
   insert into xxx values (9999, 123.951)