Sql Server 2005 primary key violation in Identity column

Question

Im working in an odd problem and I need help to figure this out.

I have a database in which there is an identifier column (defined as int is not null, Identity, starts at 1, increments by 1) in addition to all the application data columns. The primary key for the table is the column identifier, other components.

There is no data set that I can use as a "natural primary key", because the application must allow multiple representations of the same data.

I have a stored procedure, which is the only way to add new records to the table (except for logging into the server directly as the owner of db)

While QA tested the application this morning, they entered a new record into the database (using the application, as expected, and as they did over the past two weeks), and encountered a primary key violation on this table.

This is the same thing that I have been doing Primary Keys for about 10 years and have never come across this.

Any ideas on how to fix this? Or is it one of those cosmic ray failures that appears once over time.

Thanks for any advice you can give.

Nigel

Edited at 1:15 pm ET on June 12 to provide additional information.

A simplified version of the circuit ...

CREATE TABLE [dbo].[tbl_Queries]( [QueryID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](50) NOT NULL, [LastName] [varchar](50) NOT NULL, [Address] [varchar](150) NOT NULL, [Apt#] [varchar](10) NOT NULL ... <12 other columns deleted for brevity> [VersionCode] [timestamp] NOT NULL, CONSTRAINT [PK_tbl_Queries] PRIMARY KEY CLUSTERED ( [QueryID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] 

(also removed the default instructions)

The stored procedure is as follows

 insert into dbo.tbl_Queries ( FirstName, LastName, [Address], [Apt#]...) values ( @firstName, @lastName, @address, isnull(@apt, ''), ... ) 

It does not even look at the identifier column, does not use IDENTITY, @@ scope_identity or something like that, it is just a file and is forgotten.

I am as sure as I can be that the identifier value was not reset, and that no one is using direct database access to enter values. The only time in this project that uses identifier insertion is the initial deployment of the database to set specific values ​​in the lookup tables.

The QA team again tried immediately after receiving the error and was able to send the request successfully, and since then they have tried to reproduce it and still have not succeeded.

I really appreciate people's ideas.

Answer 1

It seems that the seed of identity is corrupted or reset in some way. The simplest solution would be to reset the seed to the current maximum value of the identity column:

 DECLARE @nextid INT; SET @nextid = (SELECT MAX([columnname]) FROM [tablename]); DBCC CHECKIDENT ([tablename], RESEED, @nextid); 

Answer 2

As long as I have no explanation as to the potential reason, perhaps you can change the initial value of the identity column. If the seed has been dropped to the point where the next value will already exist in the table, this may lead to what you see. Try running DBCC CHECKIDENT (table_name) and see what it gives you.

For more information, see this page.

Answer 3

Random thought based on experience

You have synchronized data, say, with Red Gate data comparison data. This allows you to re-copy the identity columns. This caused problems to use. And another project last month.

You may also have explicitly loaded / synchronized identifiers.

Answer 4

Perhaps someone inserts some records into the server directly using a new identification, and then, when the field for automatically increasing the number of identifiers reaches this number, a primary key violation has occurred.

But the cosmic ray is a good explanation;)

Answer 5

Just to be very, very confident ... you are not using IDENTITY_INSERT in your stored procedure, are you? Some logic:

 declare @id int; Set @id=Select Max(IDColumn) From Sometable; SET IDENTITY_INSERT dbo.SomeTable ON Insert (IDColumn, ...others...) Values (@id+1, ...others...); SET IDENTITY_INSERT dbo.SomeTable OFF . . . 

I feel sticky just by typing it. But every time after a while you come across people who simply never understood what an Identity column is, and I want to make sure that it is out of the question. By the way: if this is the answer, I will not hold it against you, just deleting the question and I will never admit that it was your problem!

Can you say that I hire interns every summer?

Answer 6

Do you use functions like @@ identity or scope_identity () in any of your procedures? if your table has triggers or multiple inserts, you can return the wrong identifier value for the desired table.

Answer 7

I hope this is not the case, but in SQL 2005 there is a known bug with SCOPE_IDENTITY (): http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811

Answer 8

A primary key violation does not necessarily occur from this table.

Does the application apply to other tables or call any other stored procedures for this function? Are there any triggers on the table? Or does the stored procedure itself use any other tables or stored procedures?

In particular, this can trigger an audit table or trigger.