Identity column SQL Server 2005 inserting same value twice
- by DannykPowell
I have a stored procedure that inserts into a table (where there is an identity column that is not the primary key- the PK is inserted initially using the date/time to generate a unique value).
We then use SCOPEIDENTITY() to get the value inserted, then there is some logic to generate the primary key field value based on this value, which is then updated back to the table.
In some situations the stored procedure is called simultaneously by more than one process, resulting in "Violation of PRIMARY KEY constraint..." errors.
This would seem to indicate that the identity column is allowing the same number to be inserted for more than one record.
First question- how is this possible?
Second question- how to stop it...there's no error handling currently so I'm going to add some try/ catch logic- but would like to understand the problem fully to deal with properly
