SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactional replication - which articles caused the failure


Transactional replication - which articles caused the failure

Author
Message
Casper101
Casper101
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2438 Visits: 1543
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!
Bart Lewandowski
Bart Lewandowski
Mr or Mrs. 500
Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)

Group: General Forum Members
Points: 575 Visits: 487
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?

BartL
Replication Blog
Sue_H
Sue_H
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41466 Visits: 11494
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




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search