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.

Please paste the exact error message and some sample table schema — TheGameiswar, Aug 01 '16 at 13:43
Well is the IDENTITY and int? Check the table definition. Show your vb code. — paparazzo, Aug 01 '16 at 14:33
The error implies that the IDENTITY column is trying to insert a value that's higher than 255 — Basit, Jul 20 '23 at 07:10

score 6 · Answer 1 · edited Oct 04 '20 at 09:30

6

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.

edited Oct 04 '20 at 09:30

Dilan

2,610
7
23
33

answered Oct 03 '20 at 18:51

user3407335

106
1
6

I'd suggest `select IDENT_CURRENT('dbo.TableName')` to check, source https://stackoverflow.com/a/40151083/2062726 – Michael Davidson May 18 '22 at 14:11

score 3 · Answer 2 · answered Aug 01 '16 at 13:48

3

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.

answered Aug 01 '16 at 13:48

S3S

24,809
5
26
45

It didn't crossed maximum INT value. The data is coming from MQ.. I stopped MQ connection after some time data get processed. After it came to 0. i connected it again shows same error. i noticed current column value and current identity value are different, some times those two are becoming same. I am assuming that flow is happening when both are same. – Sandeep Kumar Reddy Bade Aug 02 '16 at 04:49

score 1 · Answer 3 · answered Jan 11 '18 at 23:04

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.

Create a backup table
Insert all entries in to backup table
Truncate original table
Insert all entries from backup table
Drop backup table

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

score 0 · Answer 4 · answered Dec 23 '17 at 13:14

0

you can use this:

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

it reseed to 0.

answered Dec 23 '17 at 13:14

mahmood ansari

11

3

Could you update your answer and explain what you are doing. Many thanks in advance – MZaragoza Dec 23 '17 at 13:41

score -1 · Answer 5 · answered Aug 01 '16 at 13:49

which version of VB are you using? Are you using variable type 'long' to up the returned value in ? In VB 6 and before int is very limited in values, and in .net it is still limited to include only some 10 digit numbers.

I would make sure you are using 'long' in VB

Arithmetic overflow error converting IDENTITY to data type int

5 Answers5

Linked