DELETE A RECORD

Question

Transaction replication. We have a bad record in the PUBLISHER DB for a particular program and currently working with the app company figuring out what happened.

Anyway, this is an open ticket since JAN. We want to move forward because the bad record looks like a glitch of some sort on a process. We tried simulating on a TEST server but has not been successful. However, it is affecting a report.

My options are:

[1] Ask program owner to delete the record.

[2] We delete the record on the SUBSCRIBER DB

[3] Place a filter on the application.

For [1], we have been pushing for the program owner to remove this record but has not been successful for whatever reason. I want to implement [2] instead of [3] and ask them not do delete the record anymore.

Thoughts?

TIA

Answer 1

What exactly do you mean by "Bad record".  Are you having an error replicating this record?

Transactional replication is intended to be an exact duplicate of the data in the source system. You can add row filters. https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/filter-published-data?view=sql-server-ver15

It is not a good idea to delete records on the subscriber. This will cause errors if they update the record, and if you ever reinitialize the subscription, it will just bring the record back.

Answer 2

Hi ARPRINCE,

Deleting the records on subscriber may causes errors 20598. This error will come up anytime that data is missing at the subscriber and that given row been modified (update or delete) on Publisher. The UPDATE/DELETE commands cannot be replicated because there is no record that matches the update/delete condition on the subscriber side. 

Also as Tom said, if you ever reinitialize the subscription, it will just bring the record back.

Best Regards,
Cris

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

Hi friend,

Is there any update on this case?
Please feel free to drop us a note if there is any update.

Best regards,
Cris

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].