I have a question regarding replication and filters.
Scenario : Two tables are replicated
Table A and Table B
Table B contains a filter based on a value in Table A
So when I run a snapshot only the records in Table B that match the filter criteria are replicated which is good.
Ok so then some modifies a record in Table A
Then someone modifies a record in Table B, the filter criteria is met because the record in Table A has been amended, replication tries to send an Update command to the replicated database and it fails because the record doesn't exist in the Subscriber database ...
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)
I can change the Agent profile to ignore error 202598 and continue but that is not what I want to do.
Can anyone tell me if there's a way to make SQL Server check to see if the record exists in the subscriber and if so then perform the update, but if not then insert the record?
asked Feb 17, 2015 at 11:58 AM in Default
You can modify how insert, update and delete statements are delivered to the subscribers by providing some custom code into the process that handles your data changes. The custom code obviously isn't supported by Microsoft but if you wanted to you can role your own stored procedures or adjust the ones created to add your workflow of seeing if the record exists in the subscriber and then do an update instead of an insert when it exists.
Here are some links for more information on this subject from Books Online.
I would also strongly recommend utilizing Foreign Keys on the subscribers to also help with data quality issues that could reside from your filtering strategy across tables.
answered Feb 17, 2015 at 05:44 PM