This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(300.8, 83%, 39%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EG%3C/text%3E%3C/svg%3E)
Hi Team
I Create Transaction Replication with this scenario
1) Server Have 2 Table 1 Customer & Transaction Table
2) 2 Sub Server Have same Tables
my scenario when the transaction Happened in ( Sub Servers ) it published to server, when the server Get the Transaction it recalculate the Customer Table and Published to sub servers
So Finally Have 1 server Publisher to 2 subscribers and each subscriber Is Publisher To Server
every Thing is work Fine if the network is Connected Between Servers , When i disconnect the network and Make 1 Transaction in each sub Server and reconnect it again it work fine
but if i make 2 transaction in each sub server and reconnect the server the last sub server make transaction is get the replication from server but the another one not get it.
for example
Server Customer Fields Have QTY=10000
Sub Server1 Have Transaction Fields QTY=1000
Sub Server2 Have Transaction Fields QTY=1000
Sub Server2 Have Transaction Fields QTY=1000
when the sub servers publish transaction to Server the server reduce the amount to QTY=7000
then the Server publish the New QTY to sub servers so it must be 7000
what i show the Sub server 2 is Change the Value To 7000
but the sub server 2 QTY is 8000
in replication Monitor there is No error Get
this happened if i disconnect the server and reconnect it again and have many transaction is sub server
any suggestion to solve this
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
Additional SQL Server features and topics not covered by specific categories
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 2%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESX%3C/text%3E%3C/svg%3E)
Hi @Galaxy ,
Welcome to Microsoft Q&A!
Could you refer to this document for troubleshooting?
https://learn.microsoft.com/en-us/sql/relational-databases/replication/troubleshoot-tran-repl-errors?view=sql-server-ver15
Please post if there is any progress.
Best regards,
Seeya
Answer accepted by question author
sp_addsubscription @publication = 'TEST', ..., @German Gutierrez _detection = 'false'
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
You have 1 publisher and 2 subscribers. You should not be making changes to the rows on the subscriber database. That is not a valid case for Transactional Replication. The publisher always pushes changes to the subscribers. Not the other way around.
The Publisher only Change Table 1 " Customer" Not Table 2 " Transaction"
when i get the Transactions From Subscribers I change the Table1 " Customers Table " in Publisher Server and Publish it again
Your posts are not very clear. Are you making changes on the subscribers? Are you doing bi-directional replication?
Thanks Erland For Reply
in Picture you can see my scenario
From sub server1 & sub Server 2 I don't Make any Changes in Customer Table only Insert Transactions
From main Server when i Get the Transaction From Sub servers i Change The QTY in Customer
So how is the QTY column in Customers updated?
From Stored Procedure "sp_MSins_dboTransaction"
when get the transaction from Publisher Sub servers i update the Customer QTY in Server
So you modified the replication proc? That is not recommended. I would suggest you create a trigger on the Transaction table instead.
The problem is likely in the proc.
i Change It to trigger on the Transaction
But still Same Problem it Update Only One Sub server " one Of Subscribers Server" not All
You have 2 subscriptions updating 1 Transactions table? If so, that is not supported in Transactional Replication. You would need to have "Transactions_server1" and "Transactions_server2" tables and have triggers on each.
why the replication work fine and both of sub server updated, until if i disconnect the network and make transaction in sub servers " More one Transaction" then connect the network then i get the problem , it update in first subscriber but not update the second !!!!
if i make one transaction in both server then connect the network the update come to all subscribers
this is my point.
even i can't catch any error in monitoring replication .
there is any way to trace the distributor when publish the snap and which subscriber take the snap?
You are using transactional replication, not snapshot. In transactional replication, the commands are stored and rerun on the subscriber. When you lose connection, those commands are stored and then replayed when it detects the server is running again.
Your problem sounds like is not with the Customer pub/sub, it is with one or both of the Transaction pub/sub. You need to check the transaction table publication on the server which is not working.
However, depending on the PK of the Transaction table, you may be getting a duplicate key error. Without seeing the entire process and the table definitions it is hard to know what is working or not.
Thank You Tom for your advice
i solved it by make the Man Server is Snapshot Replication and inside the trigger For Transaction Update The Customer QTY then run the Publisher job every insert Transaction
From Sub servers To Main Server i Make Transaction Replication
PK is different Between 2 Sub servers So i Don't Have error for Transaction duplicate key.
Its work With Me, it's not perfect scenario but it's good.
i think there is a problem in Transaction Replication With Multi Subscriber i hope to fix it .
Big Thanks Tom
