Skip to main content

The row was not found at the Subscriber when applying the replicated command - Alternate workaround - SQL Server Replication

Before a week ago, I shared one post related to this title. Please read first workaround for same. I hope you liked it. Here I would like to share another way which may drive towards an alternative solution to get it resolved.

Alternate workaround :  tablediff utility
In first workaround in the last post, we used some script to get missing rows or the rows where we had an issue and applied at subscriber to complete it. But here we have another method to get missing rows and can apply at the destination. Before moving this method, I would like to read the following post related to same.
  1. The row was not found at the Subscriber when applying the replicated command-Replication error in SQL Server
  2. SQL Server tablediff Utility – Introduction
  3. SQL Server tablediff utility for multiple tables using SSIS
  4. Apply discrepancies at destination using SSIS - tablediff Utility in SQL Server
Did you read all posts? Ok, now we can go ahead. Actually, you know the workaround after reading above posts, Even let me share too. Yes, we can do it with tablediff utility. With following above links below is the script in context to the same server and following databases\tables again with transactional replication,
  • Primary database : Test
  • Secondary database : Test1
  • Replicated table : dbo.sample1
"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" 
-sourceserver [DemoServer] 
-sourcedatabase [test] 
-sourceschema [dbo] 
-sourcetable [sample1] 
-sourceuser [sa] 
-sourcepassword [test@1234] 
-destinationserver [DemoServer] 
-destinationdatabase [test1]
-destinationschema [dbo]  
-destinationtable [sample1] 
-destinationuser [sa] 
-destinationpassword [test@1234] 
-et Difference 
-f C:\DiffOutput
Make sure above statement must be in single line statement.

After running above script which will compare two tables data which have an issue (we have already script to know tables having an issue from first workaround) from source database\table and destination\table and we have generated missing rows script SQL file at C:\DiffOutput and file content as follows,
-- Host: [DemoServer]
-- Database: [test1] 
-- Table: [dbo].[sample1]
INSERT INTO [test1].[dbo].[sample1] ([id],[name]) VALUES (2,'test2') 
This will also generate delete script for the rows which exists at the destination but does not exist at source, insert script for missing rows from source to destination and update script if whole rows are different if it have, but in our case we have only insert script. Please take a note this is another alternate solution and tablediff utility may degrade performance for large tables or tables having so many numbers of rows. But this may help you to achieve your happy solution if you do not aware first workaround.