"Arithmetic overflow" error when inserting a row

Question

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)

Show your insert code please, and preferably the table schema. — Dave C
– Dave C, Commented Jun 25, 2014 at 17:24
Just because the max id is one value doesn't mean that relates to the next Identity to be assigned. Check what the last one was by executing this: SELECT IDENT_CURRENT( 'table' ) — nickles80
– nickles80, Commented Jun 25, 2014 at 17:33
I found this out on the net (dbforums.com/microsoft-sql-server/…), that truncating a table doesn't necessarily reset the seed for identity columns, and the resulting issue results in this misleading error. The issue was solved by reseeding the table, but I have no idea if this scenario applies to yours. — Jaaz Cole
– Jaaz Cole, Commented Jun 25, 2014 at 17:35
if M_ID is defined as an identity column, don't insert the value at all. The SQL Server DB will render the next value automatically. So... insert into table (from_zip, to_zip) values ('98565', '96552') Why are you trying to control the value for identity that the database sets? — xQbert
– xQbert, Commented Jun 25, 2014 at 17:35

Dave C · Accepted Answer · 2014-06-25 17:48:39Z

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.

Mark Wojciechowicz · Accepted Answer · 2014-06-25 17:42:58Z

1

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

answered Jun 25, 2014 at 17:42

Mark Wojciechowicz

4,4871 gold badge20 silver badges25 bronze badges

Collectives™ on Stack Overflow

"Arithmetic overflow" error when inserting a row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related