SQL Server Performance

TRansactional Replication: sp_MSupd Error

Discussion in 'Performance Tuning for SQL Server Replication' started by reilly, Mar 29, 2007.

  1. reilly New Member

    I have set up a simple replication job between ServerA (Publisher) and ServerB (Subscriber) both SQL Server 2000. When I set the job up initially, it ran fine over night. However, from 9 am onwards the job fails each day (but works fine when I reinitialise it).

    I am getting an error executing a batch of commands, where a command failed on sp_MSupd_TableName, the row was not found at the subscriber when applying the replicated command.

    I have applied the trace 8027 as startup parameter on ServerB, but this did not solve my issue.

    Any help on this issue would be much appreciated.
  2. reilly New Member

    Further detail:<br /><br />Connecting to Subscriber 'SQL_REPLICATOR'<br /><br />Connecting to Distributor 'XXXXXXXXXX'<br /><br />Initializing<br /><br />Error executing a batch of commands. Retrying individual commands.<br /><br />Category:COMMAND<br />Source: Failed Command<br />Number: <br />Message: {CALL sp_MSupd_TABLE_NAME (NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,02020202,0x80)} <br /><br />Category<img src='/community/emoticons/emotion-7.gif' alt=':S' />QLSERVER<br />Source: SQL_REPLICATOR<br />Number: 20598<br />Message: The row was not found at the Subscriber when applying the replicated command.<br /><br />The row was not found at the Subscriber when applying the replicated command. The step failed.<br /><br />The job failed. The Job was invoked by Schedule 23 (Replication agent schedule.). The last step to run was step 1 (Run agent.).
  3. dineshasanka Moderator

    http://msdn2.microsoft.com/en-us/library/ms151174.aspx


    This error is raised in transactional replication if the Distribution Agent attempts to update a row at the Subscriber, but the row has been deleted or the primary key of the row has been changed. By default, Subscribers to transactional publications should be treated as read-only, because changes are not propagated back to the Publisher.

    You may have to use merge replication for this

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  4. bmcloughlin New Member

  5. Jon M Member

    I agree with dineshasanka.

    But let's say you are going to stick with transactional replication, one way to prevent this from happening again is try not to give write access to users.

    As a quick work around, I resolve this kind of problem by simply reinitializing the subscription. And then from my distribution server, I rerun the snapshot.

    Jon M

Share This Page