CODEWITHSUNDEEP
sqlsql-serverssms
flerngobot
flerngobot

Reputation: 666

"Arithmetic overflow" error when inserting a row

Arithmetic overflow error converting IDENTITY to data type int.

I get this error when trying to insert a record into a table which has 656128 rows. The max id I have in this table is 1186183. This doesn't seem like numbers which would cause this error. The ID column is type int, not null. Identity, Identity Increment, and Identity Seed are all true.

Any ideas on what could be causing this?

insert into table (m_id, from_zip, to_zip)
values (7788, '98565', '96552')

schema
id (int, not null)
m_id (int null)
from_zip (nvarchar(50), null)
to_zip (nvarchar(50), null)

Upvotes: 0

Views: 1755

Answers (2)

Dave C
Dave C

Reputation: 7402

That error should only be seen when the next identity is beyond the integer limit of 2147483647.

As noted in comments, the next identity is not necessarily Max(id)+1.

It could be that someone has accidentally changed your seed value to the upper limit inadvertently.

Issue this command to check seed values:

DBCC CHECKIDENT (tableName, NORESEED)

You should see something like this:

Checking identity information: current identity value '1109', current column value '1109'.

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

If the values returned are not the same, it may pay to re-seed the identity... if the first value returned is at the limit, that is why you're getting the error, and you definitely need to reseed.

To re-seed you'd use the following to set the identity to the max(id)+1.

DBCC CHECKIDENT (tableName, RESEED)

If you wish to specify the next seed value, you can use:

DBCC CHECKIDENT (tableName, RESEED, 123456)

NB: 123456 is the current seed, so the next insert will get 123457.

Please read this msdn article before re-seeding, and understand what the implications may be for your business and data.

Upvotes: 2

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

you can check the identity: 

select IDENT_CURRENT( 'table_name' )

if you are deleting and inserting data, the identity value will continue to increment. Truncating will reset the the identity for the table.

You can change the datatype to bigint to handle larger values if that makes sense

Upvotes: 1

Related Questions