The row was not found at the Subscriber when applying the replicated command.

When you get the error as described below:

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000013DC0003277A006D00000000, Command ID: 2)

Error messages:
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598

Lookup the executed command by viewing the call (see my previous post) and update the stored procedure by commenting the rowcount check

ALTER procedure [dbo].[sp_MSdel_genTokenBalance]
  @pkc1 varchar(19)
as
begin  
 delete [gen].[TokenBalance]
where [TokenNumber] = @pkc1
--if @@rowcount = 0
--    if @@microsoftversion>0x07320000
--        exec sp_MSreplraiserror 20598
end

NOTE: don't forget to restore to the original when the problem is solved.

Good luck!

Preventing changes in one-way transactional replication

Last week we had problems with the replication. Our this weeks mission was to prevent this in the future. I've found a (really simple) way to prevent these changes which can cause duplicate keys and invalid foreign key references. With plain old triggers!

Just use the 'INSTEAD OF' together with the 'NOT FOR REPLICATION' keywords. Your triggers will fire when the action is done by everything but the replication. Also no auto increment is triggered because of the 'INSTEAD OF'. I've included the examples below.

CREATE TRIGGER TR_Account_PreventInsert on [Account]
INSTEAD OF INSERT NOT FOR REPLICATION
AS
BEGIN
RAISERROR ('Action not allowed on this Database, use the Other Database', 16, 1)
END
GO

CREATE TRIGGER TR_Account_PreventUpdate on [Account]
INSTEAD OF UPDATE NOT FOR REPLICATION
AS
BEGIN
RAISERROR ('Action not allowed on this Database, use the Other Database', 16, 1)
END
GO

CREATE TRIGGER TR_Account_PreventDelete on [Account]
INSTEAD OF DELETE NOT FOR REPLICATION
AS
BEGIN
RAISERROR ('Action not allowed on this Database, use the Other Database', 16, 1)
END
GO

The subscription(s) have been marked inactive and must be reinitialized

Yesterday we've found out that our SQL Server replication has been down for 2 days. After reinitializing the subscriptions I wanted to find out what caused the problem. After some searching on the internet I've found a very usefull Stored Procedure which can help you what the query is when you see replication errors.

use distribution
exec sp_browsereplcmds
 @xact_seqno_start = '0x<first 20 bytes of Transaction sequence number>',
 @xact_seqno_end = '0x<first 20 bytes of Transaction sequence number>'

But when the replication has been down for quite a while it's impossible to see what caused the problem in SQL. Allthough it wasn't the first thing that came to my mind, it can be found in the event log:


Hopefully this helps.