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)
33 Comments. Leave new
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
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
Check option ::SET NUMERIC_ROUNDABORT in database properties
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….
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