No duplicates exist but get Msg 2627, Cannot insert duplicate key in object

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_ATTRIBUTES
where 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?

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.

|||

No triggers exist.

The id column, the primary key column, is a uniqueidentifier.

|||Can you run the insert with just those three statements? One of the other batch inserts earlier in the order could contain an id with the same value. What you really need to do is run the select... where id in.... right before the insert, then just don't insert any rows after the insert, and see if there were rows existing the moment before statement 2 is run.|||The id value only exists in that one INSERT statement. I suspect that the last INSERT shown in SQL Profiler is not the INSERT causing the problem.|||

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.