Skip to main content

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

Earlier I wrote a post for the same error at Review of some replication issues and workaround, but it is just overview with some other replication errors. Please read it if you have not visited those posts and hope you will enjoy and help you much. Let me elaborate the error with proper example. For example, I have already created objects and configured replication, just need to use those objects. Let me introduce them,
    • Primary database : Test
    • Secondary database : Test1
    • Replicated table : dbo.sample1
I am using the same server in the example as publisher, distributor and subscriber. First need to check records in tables of both databases.
-- Selecting  records from table of publisher database 
FROM   test.dbo.sample1 (nolock) 

-- Selecting  records from table of subscriber database 
FROM   test1.dbo.sample1 (nolock) 

How to raise an error manually?
To raise mention error in tile I will remove one record from a table in subscriber database to create inconsistency, then will update same deleted records and insert one more record in table of publisher database. Let us see what will happen then,
-- Deleting one record from table in subscriber database 
DELETE FROM test1.dbo.sample1 
WHERE  id = 2 

-- Updating same record from table in publisher database 
UPDATE test.dbo.sample1 
SET    name = 'test5' 
WHERE  id = 2 

-- Inserting new record in table in publisher database 
INSERT test.dbo.sample1 
After running above script we will review replication monitor window and you will see error because it is trying update row at the subscriber side, but it is not exist and therefore it will raise an error. Due to this, new inserted records (id = 4) will not populate at subscriber side.

Now we have to do some workaround to get it resolved, but first we need to get missing row details which was deleted at subscriber side. You can see sequence number in the above image from which we can get the same information. Run following script in distribution database and see the output,
USE distribution 


FROM   dbo.msarticles m 
WHERE  EXISTS (SELECT mc.article_id 
               FROM   msrepl_commands mc 
               WHERE  mc.xact_seqno = 0x0000002200000048000300000000 
                      AND mc.article_id = m.article_id) 

EXEC Sp_browsereplcmds 
  @xact_seqno_start = '0x0000002200000048000300000000', 
  @xact_seqno_end = '0x0000002200000048000300000000' 

(Click on image to enlarge)
The result is clearly showing article and missing row details. Let me apply that missing row at subscriber side and then review replication monitor again.
-- Inserting missing record in table in subscriber database 
INSERT test1.dbo.sample1 
After inserting above record the issue will get resolved, which you can see in the image below. Missing record and the newly inserted record (id = 4) also applied,

 Hope you enjoyed this post and it will be fine you put your innovative ideas here for any alternative solution and opinion