Msdtc why

Use Distribute1. Create table T1 id int. Use Distribute2. Begin Distributed transaction. Insert into Distribute1. Insert into Distribute2. Author Recent Posts. Rajendra Gupta. As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the creator of one of the biggest free online collections of articles on a single topic, with his part series on SQL Server Always On Availability Groups.

Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra. Latest posts by Rajendra Gupta see all. Current to null so that the new connection does not pick up the transaction. After checking the stack trace, it appeared that the SQLHelper class is opening a connection to get the list of required parameters for stored procedure from database:.

In looking through the code of SQLHelper it looks like if you use the overload of ExecuteReader that takes SqlParameter instances instead of just the parameter values, then you'll save the step where SQLHelper tries to figure out what parameter maps to what value - and thereby saving a second trip to the database and the second connection and the promotion of your transaction to the MSDTC.

According to the code I'm looking at , this is the method you probably should call:. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Ask Question. Asked 8 years, 6 months ago. Active 8 years, 6 months ago. Viewed 3k times. Other situations not mentioned. ShipmentID, purchaseOrderInfo.

CreateDate, purchaseOrderInfo. CustomerNotes, purchaseOrderInfo. Status, purchaseOrderInfo. PurchaseOrderNumber which means the subsequent lines has no effect. Improve this question. Nour Nour 4, 3 3 gold badges 36 36 silver badges 62 62 bronze badges.

What are your procs doing? Do they involve linked servers? DavidHoerster I updated the question to include the procedure.

Do you have a second db connection being used? It doesn't appear to be that, but I believe that if a second connection is introduced within the TransactionScope , then the transaction gets promoted to the DTC.

MSDTC comes with necessary Operating System controls and memory structures to carry out these operations independent of the Sql Instances, while keeping integrity of the transaction across the multiple physical Sql machines a. The key point here is that these need to be Sql Instances on different physical machines.

Queries that request data across different instances on the same physical box do not go through MSDTC. If you are only going to use Database Engine, then it is not required or used.

Same rules as above apply to Sql Clusters as well with one additional rule. Since the Cluster could failover to remote machine at anytime. A query going from Instance1 to Instance2 will need MSDTC even if both the instances are active on the same physical machine B at that given point in time. This is because, there is no guarantee that they will remain on the same physical machine at any given time; They might failover to other machines, resulting in instances being on physically different machines.

Read the Microsoft links shared on this article up top. Comments RSS. You are commenting using your WordPress. You are commenting using your Google account.

You are commenting using your Twitter account. You are commenting using your Facebook account.