On SQL Server 2000, a transaction of thousands of INSERTs contains these three consecutive INSERT statements:
INSERT
INTO PARAMETER_ATTRIBUTES (ID, MIN_VALUE, MAX_VALUE)VALUES ('05b3b88a-f0f2-4e7e-a82f-73a7bd177a96', 0, 1)
INSERT
INTO PARAMETER_ATTRIBUTES (ID, MIN_VALUE, MAX_VALUE)VALUES ('05e51bee-ac3b-4257-90f1-f34adcb185cb', 0, 128)
INSERT
INTO PARAMETER_ATTRIBUTES (ID, MIN_VALUE, MAX_VALUE)VALUES ('05f33ded-979b-41ee-9d6c-ac5adc4d8635', -1000000000, 1000000000)
The transaction fails with a Msg 2627 error, Violation of PRIMARY KEY constraint 'ARR_Constraint2'. Cannot insert duplicate key in object 'PARAMETER_ATTRIBUTES'. SQL Profiler reveals that the last statement executed is the second INSERT.
The primary key is on the ID column. What is particularly significant is that before running the transaction, this query returns no rows:
select
* from PARAMETER_ATTRIBUTESwhere id in (
'05b3b88a-f0f2-4e7e-a82f-73a7bd177a96', -- pk value in first insert
'05e51bee-ac3b-4257-90f1-f34adcb185cb', -- pk value in second insert
'05f33ded-979b-41ee-9d6c-ac5adc4d8635') -- pk value in third insert
In other words, all three INSERTs contain primary key values that do not exist in the table. As you can see, each INSERT is inserting a unique value for the primary key. I am asserting that there are no duplicates. How can the fact of a Msg 2627 error be reconciled with the facts of no duplicates Can SQL Profiler not be trusted to show the specific statement causing a duplicate key problem In other words, in a transaction of 1000s of statements, does a dup key problem cause SQL Profiler to stop before it displays the statement causing the error
No duplicates exist but get Msg 2627, Cannot insert duplicate key in object
SnowGrassland
Brett Ryan
Is there, by any chance, a trigger involved
It does not look, on the surface, that you can both get no results from the select and the duplicate error. Therefore, we need to question the assumptions. Make sure that you can make the select return results from a known existant string. You might put the select after each of the selects.
What datatype are you using for the id
That is how I would approach the debugging.
Milos Cimfl - MSFT
No triggers exist.
The id column, the primary key column, is a uniqueidentifier.
John CHLee
Further testing reveals that the the duplicate key INSERT occurs 100s of lines before the last statement shown in SQL Profiler.
Here is a detailed explanation of what is happening:
1. A very popular and well regarded database update utility is being used to update 100s of very similar databases.
2. This vendor's product puts all of the 10,000s of statements in a single transaction.
3. Within the single transaction are many batches of statements.
4. Within a particular batch, there is a statement that causes a duplicate key error. Not on the QA database, not on the many real databases, just on one developer's database.
5. The last statement in the batch does not cause a duplicate key error. It was the statement I was looking at because it was the last one the SQL Profiler displayed. I also looked at the immediately preceding and following (first statement of next batch) INSERT statements. None of these statements could or did cause a duplicate key error.
What we can conclude from this is that depending on how statements are grouped and processed, the last statement displayed by SQL Profiler in an abort situation may not physically be anywhere near the statement actually causing the problem.
rgreene_nl