SQL SERVER – FIX : Error : msg 8115, Level 16, State 2, Line 2 – Arithmetic overflow error converting expression to data type

Following errors can happen when any field in the database is attempted to insert or update larger data of the same type or other data type.

Msg 8115, LEVEL 16, State 2, Line 2 Arithmetic overflow error converting expression TO data type <ANY DataType>

Example is if integer 111111 is attempted to insert in TINYINT data type it will throw above error, as well as if integer 11111 is attempted to insert in VARCHAR(2) data type it will throw above error.

Fix/Solution/Workaround:
1) Verify the inserted/updated value that it is of correct length and data type.
2) If inserted/updated value are correct modify the definition of the table to accommodated new data type length.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – 2005 – Microsoft Document Explorer cannot be shown because the specified help collection ‘ms-help://MS.SQLCC.v9
Next Post
SQL SERVER – Convert Text to Numbers (Integer) – CAST and CONVERT

Related Posts

33 Comments. Leave new

  • Hello, Sir i want guidance of how to recover deleted records from database table in Sql server i’ve refered raresql.com but i could’nt understood the code

    Reply
  • Syed Tauseef Ahmed
    December 20, 2013 2:16 pm

    Data was written to the Data Warehouse staging area but processing failed on one of the subsequent operations.
    Exception ‘SqlException’: Sql execution failed. Error 8115, Level 16, State 1, Procedure ManagedEntityChange, Line 237, Message: Arithmetic overflow error converting IDENTITY to data type int.

    One or more workflows were affected by this.

    Workflow name: Microsoft.SystemCenter.DataWarehouse.Synchronization.ManagedEntity
    Instance name: a31e6956-92a1-41cb-93aa-bc6cbc9e6f65
    Instance ID: {DFF19BC1-EF79-8E0B-B9AB-86061F88DE11}
    Management group: SCOM2012

    Reply
  • Hi Pinal,
    I have a below scenario,

    DECLARE @zero_num numeric(5,4)
    SET @zero_num = 0.00

    SELECT colA,colB,@Zero_num as ratio ,colC into #tmp
    FROM table
    UPDATE #tmp
    SET ratio = colA/ colb WHERE colC 0

    when we divide colA and ColB and assign to ratio columm iam getting overflow error because the output of the division was 248.9787565454656 .

    So can you please suggest a work around.Iam using sql server 2008 R2.Thanks in advance

    Reply
  • Check option ::SET NUMERIC_ROUNDABORT in database properties

    Reply
  • Hi Pinal,

    We have a predefined SP for DBCC CHECKDBIntegrity, few days back onwards we are facing issue stating that
    Msg 8115, Level 16, State 2, Procedure CheckDBIntegrity, Line 26
    Arithmetic overflow error converting expression to data type int.
    Msg 16917, Level 16, State 2, Procedure CheckDBIntegrity, Line 27
    Cursor is not open.
    Msg 16917, Level 16, State 1, Procedure CheckDBIntegrity, Line 35
    Cursor is not open.

    I checked in job steps EXEC CheckDBIntegrity 100;
    Go

    And I found that this one is executing in SQL server 2012 and after that I changed it to EXEC CheckDBIntegrity 120;
    Go

    but no luck, Could you please help me in this issue….

    Reply
  • I’m just trying to find some details regarding production database backups

    SELECT Database_Name,
    CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup,
    DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last,
    CASE WHEN bs.type =’D’ THEN ‘Full DB’
    WHEN bs.type =’I’ THEN ‘Differential DB’
    WHEN bs.type =’L’ THEN ‘Log’
    WHEN bs.type =’F’ THEN ‘File or filegroup’
    WHEN bs.type =’G’ THEN ‘Differential file’
    WHEN bs.type =’P’ THEN ‘Partial’
    WHEN bs.type =’Q’ THEN ‘Differential partial’
    ELSE ‘Unknown’ END AS Backup_Type, ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs, (SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs
    FROM MSDB.dbo.BackupSet bs, sys.master_files mf
    INNER JOIN sys.databases d ON d.database_id = mf.database_id
    WHERE bs.type = ‘d’
    GROUP BY Database_Name, bs.type, mf.size
    ORDER BY Days_Since_Last DESC

    If I run this on an “empty” server with just system databases it works.

    Or any other it doesn’t it gives me the error
    Msg 8115, Level 16, State 2, Line 1
    Arithmetic overflow error converting expression to data type int.

    Please help ME IM STUCK AND CONFUSED

    Reply

Leave a Reply

Menu