I am running a VB application on an SQL 2008 R2 server (on Windows Server 2008). When I run the application I get the following error when I "accept" the record.
A duplicate index or a column constraint was encountered while Saving; the transaction has been rolled back. The SQL message is: Violation of PRIMARY KEY constraint 'PK_EIAGUID'. Cannot insert duplicate key in object 'dbo.EIA'.
The statement has been terminated.
INSERT INTO EIA ( EIA_CurrentRemainingQty, EIA_ItemID, EIA_ParentItemID, EIA_Status, EIA_EmployeeID, EIA_Direction, EIA_Ref1, EIA_OriginalActivityDate, EIA_OriginalActivityQty, EIA_Description, EIA_CurrentActivityDate, EIA_Type, EIA_AccomplishedQty, EIA_GuidKey, EIA_ParentGuidKey ) Values (-2, '5N37509FCC07509', '188HINGEPINSTOCK', 'A', '176', 'OUT', '39900', '7/12/2013 12:00:00 AM', -2, '2013/07/08 0:0:0', '7/12/2013 12:00:00 AM', 'WO', 0, 'be01d058-2aad-4395-94ed-02349f1252a4', '4d85675d-0576-418c-aae6-c21d097e77e5' )
@EIA_CurrentRemainingQty = -2
@EIA_ItemID = '5N37509FCC07509'
@EIA_ParentItemID = '188HINGEPINSTOCK'
@EIA_Status = 'A'
@EIA_EmployeeID = '176'
@EIA_Direction = 'OUT'
@EIA_Ref1 = '39900'
@EIA_OriginalActivityDate = '7/12/2013 12:00:00 AM'
@EIA_OriginalActivityQty = -2
@EIA_Description = '2013/07/08 0:0:0'
@EIA_CurrentActivityDate = '7/12/2013 12:00:00 AM'
@EIA_Type = 'WO'
@EIA_AccomplishedQty = 0
@EIA_GuidKey = 'be01d058-2aad-4395-94ed-02349f1252a4'
@EIA_ParentGuidKey = '4d85675d-0576-418c-aae6-c21d097e77e5'
Command Timeout: 6000
The table "EIA" has one triger, which, oddly enough, seems to reset the warehouse ID
SET EIA.EIA_WarehouseID = parentEIA.EIA_WarehouseID
FROM inserted AS parentEIA JOIN EIA
ON parentEIA.EIA_GuidKey = EIA.EIA_ParentGuidKey
WHERE parentEIA.EIA_Type = 'WO' AND parentEIA.EIA_Direction = 'IN'
AND EIA.EIA_Type = 'WO' AND EIA.EIA_Direction = 'OUT'
My problem is that if I run the insert statement in a query, it works. The application works fine on the original server (running SQL 2008 on Windows Server 2003). How can I tell if this is an SQL setup issue or an application issue? What could cause one table to produce false duplicate key errors?
Let's start off with the basics. What type of trigger exits? Just to make certain, it would help to post the complete trigger script.
Outside of that, you will likely need to trace the statements that are executed in the batch of which your insert statement is a part. The error message is pretty straightforward - you attempted to insert a row with a PK value that already exists. Since you indicated that you can run the statement in a SSMS query window, then the logical conclusion is that the application either attempts the insert statement twice or reuses a GUID for the pk value (within the same batch, otherwise that PK value would have existed at the time you "manually" ran the insert statement).
To answer your last question - nothing but a bug (which is highly unlikely). The issue is most likely due to an application logic error.