SQL Server Error : 8115, Severity: 16. Arithmetic overflow error converting %ls

SQL Server Error : 8115 Details

SQL Server Error: 8115
Severity: 16
Event Logged or not: No
Description:
Arithmetic overflow error converting %ls to data type %ls.
Severity 16 Description:
Indicates general errors that can be corrected by the user.

Overflow – What is it? An overflow error occurs whenever we try to enter a value in a column that exceeds the data type limit. It was an identifiable column that was declared as INT in my situation, and the following are the possible error scenarios for IDENTITY columns. Everything relies on the column’s data type. The amount of possible values will depend on the type of data.

Msg 8115, Level 16, State 1, Line 25
Arithmetic overflow error converting IDENTITY to data type smallint.
Arithmetic overflow occurred.
Msg 8115, Level 16, State 1, Line 25
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred

Msg 8115, Level 16, State 1, Line 25
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.
Msg 8115, Level 16, State 1, Line 25
Arithmetic overflow error converting IDENTITY to data type bigint.
Arithmetic overflow occurred.

Explanation:

This error is usually encountered with decimal or numeric data types wherein the precision of the column or variable is not enough to hold the value being assigned to it.

Say that precision of the @DecimalValue variable is set to 4 with 2 decimal places, the highest value it can have is 99.99.  Assigning a value that has more than 2 digits on the left of the decimal place then that will generate the error:

Server: Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type numeric.

Also to note that in variables, if you assign a value where there are more decimal places than what the variable can hold, it simply rounds the value and does not generate this error:

DECLARE @one DECIMAL(4,2)
SET @one = 98.4584
SELECT @one
Returns results as  98.46

Solution :

To avoid this error, always make sure that the precision of the decimal or numeric variable that you are going to use is enough to accommodate the value being assigned to it.  Just by increasing the precision, the error can be avoided like below by increasing the decimal places to 4 from 2

DECLARE @one DECIMAL(6,4) SET @one = 100.0001

Alternate Solutions:

This error occurs due to Identity column where reseed will fix it

SQL SERVER – Below statement to check Current Identity of Table

DBCC CHECKIDENT('dbo.SmallTable')

Checking identity information: current identity value ‘255’, current column value ‘NULL’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Solution would be to reseed the column identity back to Zero  with below statement

DBCC CHECKIDENT('dbo.SmallTable',reseed,0)

Alternate errors covered

1. sql server error 8115 or sql server error 8115 identity,
2. sql server error is 8115 state 8 or  microsoft sql server error 8115,
3. sql server error code 8115 or sql server error is 8115 state 2,
4. ms sql server error 8115 or  error 8115 sql server 2012,
5. sql server error 8115 arithmetic overflow error,
6. sql server error is 8115 state 8  or error 8115 sql server

SQL Server Error Code and solution summary

SQL Server Error: 8115
Severity: 16
Event Logged or not: No
Description:
Arithmetic overflow error converting %ls to data type %ls.

Make sure decimal value inserted can be accomodated by the column of the table. increase precission if needed.