primary key constraint on autonumber?

[Taurendil]

I am suddenly unable to insert new records in a table with an autonumber field as primary key. the table already has almost 15000 records succesfully entered. when trying to insert (either from an asp page or directly on the server) i get an error trying to insert duplicate key, this is ofcourse impossible as the primary key is an autonumber field. restarting sql server or even rebooting the server doesn't seem to help
-Taurendil

 

[Tim1]

Hiya Taurendil,

The error that you are getting is not as impossible at is seems. When the autonumber was set up, was a maximum value set? Also, what increment was set? It is possible that it has used all of the values that it was allowed, and is now returning the same value each time.

You should be able to check the last value it used, by a SELECT MAX command, then if you add a command

DECLARE @value
SELECT @value = @@identity

This will put the identity value into the variable, so that you can see what it is doing.

Tim

 

[tlbroadbent]

Run DBCC CHECKIDENT on the table to correct the Identity seed.

Example:

USE pubs
Go
DBCC CHECKIDENT (jobs)
Go

Use DBCC CHECKIDENT (jobs, NORESEED) to report the value.

See the DBCC CHECKIDENT topic in BOL for more details. Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm