Don't panic
 
  

Cry about...
SQL Server Troubleshooting

Distribution Agent: Cannot insert duplicate key row in object 'TTTT' with unique index 'IIII'

Symptom:

A distribution agent has failed, the full error message text being::

Cannot insert duplicate key row in object 'TTTT' with unique index 'IIII'

where 'TTTT' is the name of a table and 'IIII' the name of a unique index of that table.

Cause:

A new row has been added at the publisher, however a row with the same key has also been added at the subscriber. When the distribution agent runs and tries to insert the new row at the subscriber it fails because a row with the same unique key already exists.

Remedy

Before attempting to remedy the problem first identify why the problem arose. Distribution agents are only used for Merge and Transaction replication. These are suitable only for propagating changes made at the publisher. Tables which are replicated in this way should not have changes made to them at the subscriber. Be aware that the offending row may have been added at the subscriber any time prior to the error first being noticed. The error will only manifest itself when a row with the same unique key is added to the publisher. This could be days, weeks or even years before.

To resolve the problem:

  1. Identify the row at the subscriber with the same unique key.

    This is actually quite easy. Within SQL Server Enterprise Manager bring up the Distribution Agent Error Details dialog. This will show the 'Last command', which will be something like:

    {CALL sp_MSins_TTTT( ... values ...)}

    where 'TTTT' is the name of the table and '... values ...' is a comma separated list of values. These are the values that it attempted to insert and failed on. Comparing these values with the primary keys or unique constraints applied to the table should allow you to identify the offending row at the subscriber.

  2. Consider if the contents of the row should be incorporated into the table at the publisher. This is a business decision.
  3. Delete the row at the subscriber (and only that row).
  4. Restart the Publication Agent for that subscriber. It should now run successfully

These notes are believed to be correct for SQL Server 7 and may apply to other versions as well.