stevenvanooyen
asked on
Primary Key Constraint Violation
In attempting a series of inserts into multiple tables, I am consistently experiencing a Primary key constraint violation - "Msg 2627, Level 14, State 1
Violation of PRIMARY KEY constraint 'aaaaatblInstitutions_PK':
Command has been aborted." This database has been upsized from MS Access 97. The first time I attempt to insert into this table and the others that accompany this insert, it works fine. After the initial time, it begins to error. Note that we are not in any way writing explicitly to the primary key which has an identity key and a seed and increment of 1. The application that is writing to the db is Cold Fusion. There is a transaction around the set of inserts and select statements. It is interesting that the error also occurs when attempting an insert within SQL Enterprise Manager Query tools.
Violation of PRIMARY KEY constraint 'aaaaatblInstitutions_PK':
Command has been aborted." This database has been upsized from MS Access 97. The first time I attempt to insert into this table and the others that accompany this insert, it works fine. After the initial time, it begins to error. Note that we are not in any way writing explicitly to the primary key which has an identity key and a seed and increment of 1. The application that is writing to the db is Cold Fusion. There is a transaction around the set of inserts and select statements. It is interesting that the error also occurs when attempting an insert within SQL Enterprise Manager Query tools.
I Have found under heavy preasure that my SQL database gave me duplicate Identities. Just before it got marked suspect.
ASKER
There has been very little pressure on any of the db's that this is happening on (all of the db's come from the same template - there is no specific interval as to when the error will begin to occur).
Have you run DBCC against your table to see if it has problems?
Do this first, using CheckTable and CheckIdent
Do this first, using CheckTable and CheckIdent
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The information was helpful. I have created a script that runs dbcc checkident on all of my tables. This works most of the time, but occasionally the problem persists past that. gmoriak talked about dropping the identity column and rebuilding it with the identity property off. I hope that there is a way that is less time consuming and a way to prevent this error.