none
Replication problem with filtered table RRS feed

  • Question

  • Hi all...
    when I active a filter on a TABLE over one that run without problems...

    1. WHERE ISNULL([Stato],'ATTIVO') = 'ATTIVO' (ok  no errors)
      but
    2. WHERE ISNULL([Stato],'ATTIVO') = 'ATTIVO' AND pubblished = 1 (see below)

    After few hours always have some rows missed in replication articles and have probelms in my process.
    pubblished is boolean field! this can be a problem?

    ------------------------------------------------------------------

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

    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

    Wednesday, December 10, 2014 12:08 PM

Answers

  • You check replication monitor and get a :

    “Transaction sequence number: 0x00E7461B000001E5000100000000, Command ID: 9″

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

    Note the sequential number will be used in the following scripts, also the commandID is important to note as not necessarily the whole sequential number has issues, it might be tied to just one command.

    Go to the distributor database en run the following command to get the list of articles involved in this issue:

    select * from dbo.MSarticles
    where article_id in (
    select article_id from MSrepl_commands
    where xact_seqno = 0x00E7461B000001E5000100000000)

    To get the whole list of commands you can run below query

    exec sp_browsereplcmds
    @xact_seqno_start = ‘0x00E7461B000001E5000100000000
    @xact_seqno_end = ‘0x00E7461B000001E5000100000000

    With this last query you can get to the exact command that is failing (by searching the command number in the commandID column)

    You will notice that a transactional replication will typically(depending on setup) use insert, delete, update stored procedures to replicate the data, so the command you will see over here will look something like:

    {CALL [sp_MSdel_dboMyArticle] (118)}

    That is the stored procedure generated to process delete statement over dbo.MyArticle table, and in this case it is trying to delete ID 118. Based on the error reported you will now realize that the issue is that the replication is trying to delete MyArtcile on ID 118 and is not there, so it is trying to delete a non existent record.

    Options:

    1. You can either check the publisher for this record and manually insert it in the subscriber, this will cause the replication command to succeed and will fix the issue.
    2. You can skip the command, for this specific example you can skip the command as there is no need to delete something that has been already deleted, by removing the command from the MSrepl_commands table. (Beware, only do this when you know what you are doing, manually removing transactions can result in a unstable replication.) In this example you would use something like
      Delete from MSrepl_commands
      where xact_seqno = 0x00E7461B000001E5000100000000 and commandID=9
    3. Reinitialize, this option is the least famous, you should try to fix the issue before doing this, however if after skipping the command you still get new errors everywhere, something definitely went wrong and there is no easy way to guarantee that your subscription is up to date and stable, this can be indicator that someone or something messed around with the data, there was some type of modification at the subscription and this is causing issues with the replication. Remember most likely a one way transactional replication is intended to have a copy of the data so it can be queried, no modification should be made to the data as this won´t replicate back to the publisher.


    Thanks

    Wednesday, December 10, 2014 1:22 PM

All replies

  • You check replication monitor and get a :

    “Transaction sequence number: 0x00E7461B000001E5000100000000, Command ID: 9″

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

    Note the sequential number will be used in the following scripts, also the commandID is important to note as not necessarily the whole sequential number has issues, it might be tied to just one command.

    Go to the distributor database en run the following command to get the list of articles involved in this issue:

    select * from dbo.MSarticles
    where article_id in (
    select article_id from MSrepl_commands
    where xact_seqno = 0x00E7461B000001E5000100000000)

    To get the whole list of commands you can run below query

    exec sp_browsereplcmds
    @xact_seqno_start = ‘0x00E7461B000001E5000100000000
    @xact_seqno_end = ‘0x00E7461B000001E5000100000000

    With this last query you can get to the exact command that is failing (by searching the command number in the commandID column)

    You will notice that a transactional replication will typically(depending on setup) use insert, delete, update stored procedures to replicate the data, so the command you will see over here will look something like:

    {CALL [sp_MSdel_dboMyArticle] (118)}

    That is the stored procedure generated to process delete statement over dbo.MyArticle table, and in this case it is trying to delete ID 118. Based on the error reported you will now realize that the issue is that the replication is trying to delete MyArtcile on ID 118 and is not there, so it is trying to delete a non existent record.

    Options:

    1. You can either check the publisher for this record and manually insert it in the subscriber, this will cause the replication command to succeed and will fix the issue.
    2. You can skip the command, for this specific example you can skip the command as there is no need to delete something that has been already deleted, by removing the command from the MSrepl_commands table. (Beware, only do this when you know what you are doing, manually removing transactions can result in a unstable replication.) In this example you would use something like
      Delete from MSrepl_commands
      where xact_seqno = 0x00E7461B000001E5000100000000 and commandID=9
    3. Reinitialize, this option is the least famous, you should try to fix the issue before doing this, however if after skipping the command you still get new errors everywhere, something definitely went wrong and there is no easy way to guarantee that your subscription is up to date and stable, this can be indicator that someone or something messed around with the data, there was some type of modification at the subscription and this is causing issues with the replication. Remember most likely a one way transactional replication is intended to have a copy of the data so it can be queried, no modification should be made to the data as this won´t replicate back to the publisher.


    Thanks

    Wednesday, December 10, 2014 1:22 PM
  • For some reason the replication process is expecting some rows to be in place on the subscriber and can't find them there. It is unlikely that your filter is causing the problem, but more likely the problem is that somehow there subscriber data has been changed. Does anyone else make changes on this database? Is it possible that the article is in another publication?

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Wednesday, December 10, 2014 6:07 PM
    Moderator