0

From yesterday our Data Warehouse running on Azure SQL Server getting the error:

Cannot insert duplicate key row in object 'odl.Dim_TraceBackFinishedProduct' with unique index.

Error details:

SQL - Fail Parent:Error: Executing the query "RAISERROR('Cannot insert duplicate key row in obje..." failed with the following error: "Cannot insert duplicate key row in object 'Dim_TraceBackFinishedProduct' with unique index 'PK_TraceBackFinishedProduct_TraceBackFinishedProductSK'. The duplicate key value is (-2147293203).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly

Scripte to create table:

CREATE TABLE odl.Dim_TraceBackFinishedProduct
    (
    TraceBackFinishedProductSK int IDENTITY(-2147483648,1) NOT NULL,
    TraceBackFinishedProductBK binary(32) NOT NULL,
    <and many other columns>
    
) ON PRIMARY

GO

Script to create index:

CREATE UNIQUE CLUSTERED INDEX PK_TraceBackFinishedProduct_TraceBackFinishedProductSK 
    ON odl.Dim_TraceBackFinishedProduct (TraceBackFinishedProductSK ASC)

WITH (
     PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
     DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
    ) ON PRIMARY
GO

I am not an experienced SQL DBA, so I have just rebuilt the index but the issue still insists on.

Any your guidance is much appreciated. Thank you in advance

Peter

0

Looks like you are trying to insert a row in your table with value for the column : TraceBackFinishedProductSK.

In the definition of your table you have defined it to be unique and identity as well as an auto incrementing integer starting with lowest value of -2147483648 and max value 1. That means you do not need to insert data with this column, it will get a value automatically. for eg.

INSERT INTO Dim_TraceBackFinishedProduct (TraceBackFinishedProductBK ) VALUES (<whatever is your example value>);

If you use the above insert you should not get the error message.

The error message also tells, you are trying to insert a row with key -2147293203, but a row already exists with this key. Either you have to change the key or let the database create the key for you automatically on insert as explained above in the insert example.

2
  • Hi mna, thank you very much for your suggestion. The table is automatically inserted by ETL and has run smoothly for over one year. I has not changed on the table or source, but from yesterday the error has happened. So I am not sure what is the best solution to solve the issue now. – Peter Nguy Nguyen Jan 12 at 7:43
  • In that case, my guess would be that your ETL process might have probably crashed and tried to reinsert some of the rows. I would recommend checking the logs if there was a crash. – mna Jan 12 at 9:18
0

I have used the built-in function DBCC CHECKIDENT to reset the identity value (surrogate key which used to create unique index) on the table and the DW is working fine now. https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-ver15

1
  • A resetting and new indexing is always very tricky and shouldn't be done without testing it (like any other major change) – nbk Jan 12 at 23:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.