Okay, a little complex:
- One local DB table is being replicated to another local DB with same table. Worked for years. User account on replication job is gone, so job was failing. I updated it to a different account, and every 10 minutes, it had no errors then. However, it didn't replicate a new row.
- I manually created the new record in the subscriber DB table.
- Restarting the server ended up working, but now the replication fails because it has the queue of new records it's trying to add and the identity column value already exists in the destination table.
Options: Should I…
- Delete the records I added so that the replication can copy down the new records?
- Problem: Not sure if the used IDs will be able to be used with the new records coming over in the replication (because of the auto-numbering identity column), and we need those IDs to remain matched.
- Somehow clean out the queue so that those records aren't attempted any more (since I already created them) — not sure how to do this.
Yes, I searched a lot on this stuff, but I haven't seen this problem described in the same way.
Thanks for any help or answer!
Best Answer
You do not need to drop the subscription.
You're getting primary key violations as the replication agent is trying to insert duplicates.
Yes, you will need to manually remove the records in order to alleviate the primary key violations.