none
Arithmetic overflow error converting numeric to data type numeric

    Question

  • Just noticed very wierd thing today while working on Store Proc in SQL server 2008.

    My proc returned me "Arithmetic overflow error converting numeric to data type numeric" in the middle of the code but it ALSO ran completely to the end.

    I thought that if we get errors, the SP should immediately return but how its possible that it completed the rest of statements too?

    Any suggestions!

    Thursday, March 31, 2011 12:44 AM

Answers

  • Take a look at SET ANSI_WARNINGS topic in BOL. This message is a warning message which does not cause the execution of the SP to stop.

    SET ANSI_WARNINGS affects the following conditions:

    • When set to ON, if null values appear in aggregate functions, such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT, a warning message is generated. When set to OFF, no warning is issued.

    • When set to ON, the divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When set to OFF, the divide-by-zero and arithmetic overflow errors cause null values to be returned. The behavior in which a divide-by-zero or arithmetic overflow error causes null values to be returned occurs if an INSERT or UPDATE is tried on a character, Unicode, or binary column in which the length of a new value exceeds the maximum size of the column. If SET ANSI_WARNINGS is ON, the INSERT or UPDATE is canceled as specified by the ISO standard. Trailing blanks are ignored for character columns and trailing nulls are ignored for binary columns. When OFF, data is truncated to the size of the column and the statement succeeds.


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    • Proposed as answer by DVR Prasad Thursday, March 31, 2011 9:03 AM
    • Marked as answer by Me.Saqib Thursday, March 31, 2011 7:31 PM
    Thursday, March 31, 2011 1:34 AM

All replies

  • Take a look at SET ANSI_WARNINGS topic in BOL. This message is a warning message which does not cause the execution of the SP to stop.

    SET ANSI_WARNINGS affects the following conditions:

    • When set to ON, if null values appear in aggregate functions, such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT, a warning message is generated. When set to OFF, no warning is issued.

    • When set to ON, the divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When set to OFF, the divide-by-zero and arithmetic overflow errors cause null values to be returned. The behavior in which a divide-by-zero or arithmetic overflow error causes null values to be returned occurs if an INSERT or UPDATE is tried on a character, Unicode, or binary column in which the length of a new value exceeds the maximum size of the column. If SET ANSI_WARNINGS is ON, the INSERT or UPDATE is canceled as specified by the ISO standard. Trailing blanks are ignored for character columns and trailing nulls are ignored for binary columns. When OFF, data is truncated to the size of the column and the statement succeeds.


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    • Proposed as answer by DVR Prasad Thursday, March 31, 2011 9:03 AM
    • Marked as answer by Me.Saqib Thursday, March 31, 2011 7:31 PM
    Thursday, March 31, 2011 1:34 AM
  • See the ARITHABORT and ARITHIGNORE  options which together with ANSI_WARNINGS
    define the behavior when this is encountered:
     

    Plamen Ratchev
    Thursday, March 31, 2011 2:00 AM
  • So either way if ANSI Warning OFF/ON, the SP will be completed.

    Its just matter of pref you want to receive warnings or implicit behaviors.

    Thursday, March 31, 2011 2:07 AM
  • When an error occurs in SQL Server, this can happen:

    1) The connection is terminated.
    2) The batch is aborted and the transaction is rolled back.
    3) The batch is aborted but the transaction is rolled back (new in Denali).
    4) The current scope is aborted. No transaction is rolled back.
    5) The current statement is terminated and rolled back, but any open transaction remains open.

    There are even some more intricate variations. The first only happens with serious internal errors. And 4 happens with compilation errors. 3 relates to the new THROW statement.

    But which happens of 2 and 5 is willy-nilly. That is, it is not random; for any given situation, it is deterministic what SQL Server chooses. But trying to find any rhyme or reason in those choices is about impossible.

    If you use TRY-CATCH that mitigates the situation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Thursday, March 31, 2011 7:59 AM
  • When an error occurs in SQL Server, this can happen:

    1) The connection is terminated.
    2) The batch is aborted and the transaction is rolled back.
    3) The batch is aborted but the transaction is rolled back (new in Denali).
    4) The current scope is aborted. No transaction is rolled back.
    5) The current statement is terminated and rolled back, but any open transaction remains open.

    There are even some more intricate variations. The first only happens with serious internal errors. And 4 happens with compilation errors. 3 relates to the new THROW statement.

    But which happens of 2 and 5 is willy-nilly. That is, it is not random; for any given situation, it is deterministic what SQL Server chooses. But trying to find any rhyme or reason in those choices is about impossible.

    If you use TRY-CATCH that mitigates the situation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Thursday, March 31, 2011 8:00 AM
  • Hi Erland,

    I dont see any diff in points 2 and 3.

    Thursday, March 31, 2011 3:34 PM
  • I guess Erland meant to say "The batch is aborted but the transaction is NOT rolled back (new in Denali)."

     


    AMB

    Some guidelines for posting questions...

    Thursday, March 31, 2011 3:48 PM
  • > I guess Erland meant to say "The batch is aborted but the transaction is
    NOT rolled back (new in Denali)."

    Yup. Thanks for reading my mind, Alejandro.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Thursday, March 31, 2011 9:50 PM