Transactional Replication : How to find erroring command and remove it

Had an interesting situation today where a replicated stored procedure call was in error. The issue was that the procedure call had some single quotes in a string parameter. This was giving the following error in Replication Monitor:

Error messages:
Invalid distribution command, state 2: transaction 0x0x000007c000005e36005000000000 command 3{offset : 258 token offset: 44 state: 50) }. (Source: MSSQL_REPL, Error number: MSSQL_REPL21001)
Get help: http://help/MSSQL_REPL21001

There is actually everything you need in that error, the transaction sequence number is the key piece of information.

But out of interested, I listed up the replication errors at the Publisher using sp_helpsubscriptionerrors as follows:

exec sp_helpsubscriptionerrors  @publisher = 'myPublisher', @publisher_db = 'dbName', @publication='publicationName', @subscriber='mySubscriber', @subscriber_db='dbName'

(I've obviously changed the parameters to hide the sensitive bits!)

This command lists up all of the failed transactions for the subscription. In a normal implementation, this may be just one. However if you have multi-threaded your subscription then there may be more than one.

Once the offending command has been identified (and it will be the same as the one reported in Replication Monitor) you can decide what to do. In this case I wanted to delete the command and apply it by hand (minus the single quotes!). There is a nice command which allows this, the important piece of information is the xact_seqno (otherwise known as the transaction sequence number). sp_setsubscriptionxactseqno will remove the command at the subscriber. Yep, not obvious from the name of the command. The crucial bit to note here is you have to run it in the subscriber database, it will not work from the distributor.

exec sp_setsubscriptionxactseqno @publisher = 'myPublisher',@publisher_db ='publicationName', @publication='mySubscriber', @xact_seqno = 0x000007C000005E36005000000000