November 11, 2004 at 6:39 am
We have an application that inserts small groups of records (one record at a time) into a table periodically throughout the day via stored procedure. The key of that table is simply an identity field. As many as 5% of the inserts are intermittently failing with error 2627 (Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.) How can an identity field yield a duplicate value on insert? The same records that get failed this way can be recreated and successfully inserted a few minutes later. We are running SQL Server 2000, SP3a.
GG
November 11, 2004 at 8:35 pm
If you are able to reproduce this error in Query Analyser, the message text will be more helpful (the meaningless variable placeholders will be replaced by the actual constraint and object names), so you might gain more insight into what is happening.
If you are having a problem with direct inserts into an auto-incrementing identity field, I would suspect the application, given that this technique is very common across SQL Server apps. Do you have the source code of the app? In which case you can check for 'unusual' coding techniques that might be causing issues (eg set identity insert).
You could also run Profiler to try and trap the statement that causes the error and work back from there.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 21, 2008 at 11:21 pm
Sounds like you have a datetime as a part of a unique constraint or pk.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply