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

The row was not found at the Subscriber when applying the replicated command  Transaction Replication error 

Demo 1:

     Primary Server: USER-PC\SQL 2012 E N T P E D T

     Secondary Server: USER-PC\SQL 2012 E N T P TEST

• Primary database : Sales DB Rep Pub l

• Secondary database : Sales db R e p l Sub s c

• Replicated table : Customers

Source Server:

 Destination  Server:

I am using the same server in the example as publisher, distributor and subscriber. First need to check records in tables of both databases. 

To create above error manually
To raise mention error in The row was not found at the Subscriber when applying the replicated command 

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 below


--Delete the one record from Subscriber

delete from [Sales dbReplSubsc].d bo.Customers where Customer ID=19770

--updating the same record from Publisher

Update [SalesDB RepPubl].d bo. Customers set First Name='fname 19770',Last Name='fname 19770' where Customer ID=19770

--Inserting new record to the publisher
set identity_insert [Customers] on
Insert into Customers(customer id,First Name,Middle Initial,Last Name)
select 19772,'test 2','t 2','Test name2'

set identity_insert [Customers] off

--View the details from table

SELECT @@server name,* FROM [d b o].[Customers]

order by Customer ID desc

full details as see below

Now let start the snapshot agent and log reader agent and distribution agent job as see below

Snapshot agent Job:

Log Reader Agent:

Distribution Agent:

After running above script we will review replication monitor window and you will see error because it is trying update row 19770 at the subscriber side, but it is not exist(i.e we removed 19770 from subscriber)  and therefore it will raise an error. Due to this, new inserted records (id = 19772) from publisher will not populate at subscriber side as see below

So we got Error as The row was not found at the Subscriber when applying the replicated command

we can click monitor button to see more info

To Resolve this Error:

Solution:

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

select * from M S repl_errors

order by time desc

note down the error sequence number Replication monitor

or

Note down from below screen shot from ms replication error table

then kindly give it sequence no in below query and run it

use distribution

SELECT  art.publisher_id ,
        art.publisher_db ,
        art.publication_id ,
        art.article ,
        art.article_id  ,
        art.destination_object  ,
        art.source_owner  ,
        art.source_object 
FROM    distri bution. dbo.MSarticles AS art
        JOIN distri bution.dbo.MSrepl_commands AS com 
        ON art.Article_id = com.Article_id
WHERE   com.x act_seqno =  0 x0000017600001BE9000300000000-->place it seq no here

(or)

(or)

USE distribution 

go 

SELECT * 
FROM   d b o.m s articles m 
WHERE  EXISTS (SELECT m c.article_id 
               FROM   m s r e p l_commands m c 
               WHERE  m c.x act_seq no = 0 x 0 x0000017600001BE9000300000000
                      AND m c.article_id = m.article_id) 

now paste it above publisher database id and article id in below query and run it

use distribution

EXEC distribution.d b o.s p_browse r e p l cm d s 
     @x act_seq no_start = '0 x 0 x0000017600001BE9000300000000' ,
     @x a ct_seq no_end = '0 x 0 x0000017600001BE9000300000000' , 

     @publisher_database_id = 1 , 
     @article_id = 1 , 

     @command_id = 1 ----------Command id always 1(optional)

So this is the command we need to check it on both publisher and subscriber

{CALL [s p_MS up d_d b o Customers] (,N'f name 19770',,N'f name 19770',19770,0 x 0 a)}

it says above 19770

-- Inserting missing record in table in subscriber database




set identity_insert [Customers] on Insert into Customers(customer id,First Name,Middle Initial,Last Name) select 19770,'f name 19770','f','f name 19770' set identity_insert [Customers] off

Demo 2:

Earlier I wrote a post for the same error at Review of some replication issues andworkaround, 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. 

?

1 2 3 4 5 6 7

-- Selecting  records from table of publisher database SELECT * FROM   test.dbo.sample1 (nolock)   -- Selecting  records from table of subscriber database SELECT * 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,

?

1 2 3 4 5 6 7 8 9 10 11 12 13

-- 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 SELECT 4,        'test4'

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.

Solution 
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,

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

USE distribution   go   SELECT * 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'

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.

?

1 2 3 4

-- Inserting missing record in table in subscriber database INSERT test1.dbo.sample1 SELECT 2,        'test2'

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,

Thanks for reading

Ref:
http://www.sqlideas.com/2015/05/the-row-was-not-found-at-subscriber.html