none
Arithmetic overflow error converting numeric to data type numeric in SQL Server - How to resolve this error in sql 2016 RRS feed

  • Question

  • Hi 

    i have 2 sql 2016 instances in both the  instances i tried executing the below query 

     select CONVERT(decimal(5,2),4.000000) 

    In one SQL 2016 instance its displaying the output, 

    where in other SQL 2016 instance its throwing an error 

    Arithmetic overflow error converting numeric to data type numeric 

    Is there any where i should change the settings to clear the  error in one of my sql 2016 instance , If yes please suggest me the place where i  need to change. 

    thanks 


    hemadri

    Friday, July 5, 2019 10:09 AM

All replies

  • try running 

    SELECT @@VERSION

    and post the result from both server instances here to know if there's any differences in version/build


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, July 5, 2019 10:51 AM
  • Is there any where i should change the settings to clear the  error in one of my sql 2016 instance , If yes please suggest me the place where i  need to change. 

    Depending on the NUMERIC_ROUNDABORT and ARITHABORT session settings, an error is generated when a loss of precision occurs (rounding the decimal value in this case). This is detailed in the documentation (https://docs.microsoft.com/en-us/sql/t-sql/statements/set-numeric-roundabort-transact-sql).

    You can view the current session settings by executing:

    DBCC USEROPTIONS;

    This script shows the behavior with these settings on and off:

    SET NUMERIC_ROUNDABORT ON;
    SET ARITHABORT OFF;
    SELECT CONVERT(decimal(5,2),4.0000); --error: Arithmetic overflow error converting numeric to data type numeric.
    GO
    
    SET NUMERIC_ROUNDABORT ON;
    SET ARITHABORT ON;
    SELECT CONVERT(decimal(5,2),4.0000); --error: Arithmetic overflow error converting numeric to data type numeric.
    GO
    
    SET NUMERIC_ROUNDABORT OFF;
    SET ARITHABORT OFF;
    SELECT CONVERT(decimal(5,2),4.0000); --no errors
    GO
    
    --required for indexed views, filtered indexes, indexes on computed columns
    SET NUMERIC_ROUNDABORT OFF;
    SET ARITHABORT ON;
    SELECT CONVERT(decimal(5,2),4.0000); --no errors
    GO

    The session settings are usually specified by the client such that you would not get the error with default settings and modern APIs. As to a configuration option that may affect these session settings, check 'user options` (https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-user-options-server-configuration-option). Bits 64 (ARITHABORT ON) and/or  8192 (NUMERIC_ROUNDABORT) may be set to a non-zero value. There are also database options to turn the setting on for clients do not specify the option when connecting. You can view the config option with:

    EXEC sp_configure 'user options';


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Friday, July 5, 2019 11:32 AM