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
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
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,
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,
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,
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.
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
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:
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
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' |
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' |
Thanks for reading
Ref:
http://www.sqlideas.com/2015/05/the-row-was-not-found-at-subscriber.html
