Transactional replication - which articles caused the failure

  • Hi

    I have a Push Transactional replication process. I use the GUI to add multiple stored procs as articles. But during the sync process to the subscriber it fails with different messages, depending on what is wrong.
    One example is a proc that errors with Unable to replicated .... "Column name or number of supplied values does not match table definition"
    This proc is clearly broker and I don't want to replicate it - but I am not sure which proc it is.
    My question is:
    When Transactional replication fails, where can I see on which object it failed? I have queried various tables but none from what I can see, pinpoints the exact object (article) name where the failure occurs

    any help would be greatly appeciated

    Thanks!

  • Hi,

    Is the error occurring when delivering snapshot or just when normal replication is delivering transactions?  In error u getting LSN/seqno? If so u can try to browse replicated commands in distribution database.
    https://bartoszlewandowski.blog/2017/08/08/how-to-browse-distributor-sp_browsereplcmds-explained/

    Seems like you have additional columns in some tables that are touched by your procedures that u are replicating... the schema of tables is different between Publisher and Subscriber.

    You only replicate stored procedures?

  • Casper101 - Friday, December 22, 2017 3:36 AM

    My question is:
    When Transactional replication fails, where can I see on which object it failed? I have queried various tables but none from what I can see, pinpoints the exact object (article) name where the failure occurs

    any help would be greatly appeciated

    Thanks!

    The easiest way is to use sp_browsereplcmds in the distribution database  - pass in the publisher_database_id and also get the xact_seqno and command_id from the error in the Agent to pass in.
    This stored procedure generally queries MSrepl_commands as the table has the article id and the xact_seqno.
    You could try to figure it out on your own with MSrepl_commands. The error message would be in MSrepl_errors which also has the xact_seqno and command_id

    Sue

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply