Once in a while you may find the need to skip an indiviual transaction in SQL 2005/2008 Transactional Replication. For example, when Distribution Agent fails because of an error and the database integrity won't be impacted by skipping that transactions.  For instructing the Distribution Agent to skip a transaction, SQL provides 2 very helpful stored procedures.  sp_helpsubscriptionerrors can be used to display the list of invalid transactions.  The key output from this SP is the xact_seqno which is supplied to the second stored procedure sp_setsubscriptionxactseqno as the transaction LSN or sequence number to be skipped. 

sp_helpsubscriptionerrors

http://msdn.microsoft.com/en-us/library/ms173427.aspx

sp_setsubscriptionxactseqno

http://msdn.microsoft.com/en-us/library/ms188764.aspx

Note the BOL topic incorrectly says "Is the LSN of the next transaction at the Distributor to be applied at the Subscriber" when it is actual the LSN of the transaction the Distributor will SKIPP.  We're working to get BOL updated.

EXAMPLE

sp_helpsubscriptionerrors 'SQLw2k8','dbtranpub','dbtranpub_pub','SQLw2k8Subscriber','dbtransub':

id          time                    error_type_id source_type_id source_name   error_code   error_text                                   xact_seqno                         command_id  session_id

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

16          2009-09-08 05:14:04.673 0             0              MSSQL_ENG     8152         String or binary data would be truncated.    0x0000002B00000196000300000000     1           0

16          2009-09-08 05:14:04.660 0             0              MSSQL_ENG     8152         String or binary data would be truncated.    0x0000002B00000196000300000000     1           0

16          2009-09-08 05:14:04.660 0             1              MSSQL_ENG                  if @@trancount > 0 rollback tran             0x0000002B00000196000300000000     1           0

Command to skip the transaction:

sp_setsubscriptionxactseqno 'SQLw2k8','dbtranpub','dbtranpub_pub',0x0000002B000001960003

Many thanks to Amit Banerjee, Microsoft SQL Technical Lead, for the example and testing of these SPs.

Chris Skorlinski, Microsoft SQL Server Escalation Services