Diagnosing Replication Issues

I'm in the process of creating a Transaction Replication solution that incorporates some transformation. The condensed version is that the publisher data structure is nothing like the subscriber structure. To do this I have replaced the standard sp_MSins/del/upd stored procedures with my own coding. There is some simple transformation going on in these sp's, and debugging it is not nice.

So, imagine my delight when an article from SqlServerCentral dropped into my Inbox - couldn't have been better time.

The important bit for me was establishing where the problem lies and what to do with it. The way to do this is as follows:

In Replication Monitor examine the error message in the detail screen. It will have something like this :

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

Error messages:
etc.

The bit to note is the Transaction sequence number and Command ID. You can drop the trailing 0's from the sequence number.

Next SELECT FROM msdistribution_agents and get the publisher_database_id. Note this number, you'll need it.

Finally, call the procedure sp_browsereplcmds :

exec sp_browsereplcmds
@xact_seqno_start = '0x0000018F0000008800A7',
@xact_seqno_end = '0x0000018F0000008800A7',
@publisher_database_id = 15,
@command_id = 83

Examine the contents of the command column - this is what is being sent to your stored procedure. You can now run this by hand and see what the issue is. I recommend using a transaction and rolling back so that you do not introduce other errors.