Arithmetic overflow error converting IDENTITY to data type int

Question

Arithmetic overflow error converting IDENTITY to data type int, is occurring when running through vb code. But when I execute the stored procedure, data gets inserted properly.

Through application also sometimes this works and sometimes it doesn't.

When I check the current identity value and current column values are different. IS this falls under issue.

Please let me know how to solve it.

Solution 1

Please DO NOT use

DBCC CHECKIDENT('dbo.TableName',reseed,0)
//or 
DBCC CHECKIDENT('dbo.TableName')

to troubleshoot this like I did. It will cause the database to re-set your identities.

Please use this

DBCC CHECKIDENT('dbo.TableName',NORESEED)

It will show if your table is at the maximum for int values. The maximum for int is 2,147,483,647.

Solution 2

2147483647 Is the maximum size for INT so somewhere your IDENTITY is larger than this. You can use BIGINT instead to get around your error, assuming your IDENTITY won't surpass 9223372036854775807.

Solution 3

Like others have said, your column is greater than the range allowed for the type.

You could reseed using DBCC CHECKIDENT('dbo.TableName',reseed,0) like others have said, but that could cause errors if you attempt to reuse a value that has already been used.

Assuming the column is an auto increment identity column and your row count is lower than the data type range, you could reinsert all of the values.

1. Create a backup table
2. Insert all entries in to backup table
3. Truncate original table
4. Insert all entries from backup table
5. Drop backup table

This will depend on your constraints and may be more difficult than it is worth.