Exploration into “Row was not found”

 

Chris Skorlinski
Microsoft SQL Server Escalation Services.

“The row was not found at the Subscriber when applying the replicated command.” errors reported by the SQL Replication Distribution Agent can often be some of the hardest issues to troubleshoot.  With great satisfaction I was able to uncover this mysterious error for one of my customers.

In the customers SQL Replication topology they’re consolidating 2 databases from a single Publisher into 1 database on the Subscriber.  The published tables going into different “schema” or groups on the Subscriber.

Database A, Publication A into Subscriber schema A.{table}
Database B, Publication B into Subscriber schema B.{table}

They subscriber tables are unique only by the “schema” or group name.  For this topology, (2 into 1) is possible, however, to make this work “special” handling is required or Distribution Agents will fail with “missing data” and/or “invalid data” in the Subscriber. 

Here is why:

By default SQL Server Replication creates stored procedures on the subscriber to update the subscriber tables.  In my example I’m replicating the CUSTOMER table.  The default stored procedure created by Replication to update the subscriber is called [dbo].[sp_MSupd_dboCustomer]. As you can see there is no A or B schema naming.  Therefore each publication will use the same stored procedure name on the subscriber, however, the whichever stored procedure is created LAST will get ALL of the data changes. 

If I first create A publication, the stored procedure [dbo].[sp_MSupd_dboCustomer] updates  [A].[Customer] table.  Next if I create the B publication and push to the same subscriber database, the stored procedure created by B publication is created with the same name as A publication, over writing the A stored procedure.  As result ALL, yes ALL changes from both A and B are routed to the [B].[Customer] table.  Not only is A missing inserts, those inserts are being apply to B.Customer table.

create procedure [dbo].[sp_MSupd_dboCustomer]
update [A].[Customer] set
where [CustomerID] = @pkc1

create procedure [dbo].[sp_MSupd_dboCustomer]
update [B].[Customer] set
where [CustomerID] = @pkc1

Solution

To correct this problem ALL publications need to be DROPPED and re-created with their own UNIQUE stored procedures on the subscriber.  You can accomplish this via the Replication Wizard by editing the “INS/UPD/DEL stored procedure” names under the “Statement Delivery”.

image

 

Or by modifying the CREATE PUBLICATION script, then sp_addarticle command to specify a unique name for each of the INS, DEL, and UPD commands.

clip_image004

 

-- Adding the transactional articles (Publication A)

exec sp_addarticle @publication = N'A', @article = N'Customer', … , @ins_cmd = N'CALL [sp_MSins_A_Customer]', @del_cmd = N'CALL [sp_MSdel_A_Customer]', @upd_cmd = N'SCALL [sp_MSupd_A_Customer]'

GO

-- Adding the transactional articles (Publication B)

exec sp_addarticle @publication = N'B', @article = N'Customer', … , @ins_cmd = N'CALL [sp_MSins_B_Customer]', @del_cmd = N'CALL [sp_MSdel_B_Customer]', @upd_cmd = N'SCALL [sp_MSupd_B_Customer]'

GO

 

After these changes 2 stored procedures for updating the subscriber tables will be created, one from each publication updating only that publications data.