question

rldukes avatar image
0 Votes"
rldukes asked AndersonDukesRobert-9251 answered

Change Tracking causes errors to appear 'Cannot insert duplicate key row in object'

Hi,

I am using SQL 2019 Enterprise Version. I have a simple process that runs with no errors updating / merging between tables in the same database. If i enable change tracking on one of the destination tables the process starts to fail with the error Cannot insert duplicate key row in object ' XXXXX ' with unique index '. As soon as i disable the change tracking everything works fine again. There are no errors in the log.

Anyone else experienced this issue, or know how to resolve the problem?

Thank you!

sql-server-generalsql-server-transact-sql
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

and just to add, there is no key conflicts or unique indexes. When the process runs with no change tracking everything works. As soon as change tracking is enabled the above error appears.

0 Votes 0 ·

What is that XXXXX? Is that one your own tables you have masked out, or is it one of the internal tables that Change Tracking uses?

0 Votes 0 ·

Sorry yes, thats the XXXX is our table name not the internal table.

0 Votes 0 ·

Hi @rldukes,

Welcome to Microsoft Q&A!

Not sure if it is due to some reason between the merge statement and change tracking.Maybe you can show the sql statements and simple table structure examples and data so that I can try to reproduce this problem in my own environment.

0 Votes 0 ·

I'll see if i can try to provide a sample of the query and the table, but the merge statement is just a normal merge statement updating/inserting from a temp table. We run it several times during the day and there are no issues. As soon as we add change tracking on then we get errors about a unique index duplicate key even though we have no unique indexes applied.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered rldukes commented

It certainly sounds like a bug in the product.

I'm afraid that we cannot do much here. Well, we could ask you to post a repro that demonstrates the issue, but that could take you a bit of time. And in the end we might only say "yes, we can repro this", but unless there is something that stands out as special, I don't think that we will be able to help you to get around the problem.

So I would advice you that you first check "SELECT @@version" and verify that you are on CU11 of SQL 2019. If you are not, apply CU11, which is the most recent CU for SQL 2019, to see if that it helps.

If it does not, open a support case, if this is a blocking issue for you. (And I guess it is.)

Well, an alternative is of course to replace the MERGE with individual INSERT and UPDATE statements, and there are blog posts out there that gives MERGE bad press. But I find MERGE quite nifty myself, so I am not keen on that workaround.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your reply - Yes we are on CU11, and plan to raise a support case hopefully today as it is starting to block. Hopefully we can resolve it before replacing our merges though to inserts/updates and i feel it is a bug either with SQL or with how its handling our code possibly. Will provide an update once we know more too.

0 Votes 0 ·
AndersonDukesRobert-9251 avatar image
0 Votes"
AndersonDukesRobert-9251 answered

Just to add an update, we removed all the merge statements and are just using updates and inserts, and still seeing the same message. We also upgraded to the latest cu12 and still seeing the same issue.

I'm reaching out to Ms today hopefully to get some assistance on it.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.