Why is MSDTC involved?

Question

As far as I know, The MSDTC only gets involved when:

You're querying a view/table inside a transaction that is linked to another server.
You're using two SqlConnections (or whatever it is NHibernate uses) within a single TransactionScope
You're enlisting another transactional component (like MSMQ or the transactional file system) inside a TransactionScope.
Other situations not mentioned.

If I disable MSDTC and run the following code, I get (MSDTC on server 'Server' is unavailable) an error.

public bool Add(PurchaseOrderInfo purchaseOrderInfo)
{
    using (TransactionScope ts = new TransactionScope())
    {
        using (SqlConnection Cnn = new SqlConnection(SqlHelper.ConnStr))
        {
            Cnn.Open();

                try
                {
                    using (SqlDataReader rdr = SqlHelper.ExecuteReader(/*Tr*/Cnn, "spPurchaseOrderAdd", purchaseOrderInfo.ExpectedShipment.ShipmentID, purchaseOrderInfo.CreateDate, purchaseOrderInfo.CustomerNotes, purchaseOrderInfo.Status, purchaseOrderInfo.PurchaseOrderNumber))
                    {
                        if (rdr.Read())
                            FillPurchaseOrderInfo(rdr, ref purchaseOrderInfo, GettingDepthEnum.Level_0);
                        else
                        {
                            return false;
                        }
                    }

                    foreach (PurchaseOrderDetailInfo detailInfo in purchaseOrderInfo.Details)
                    {
                        throw new Exception("Test");
                        //if (!AddPurchaseOrderDetail(Tr, purchaseOrderInfo, detailInfo))
                        {
                            //Tr.Rollback();
                            return false;
                        }
                    }

                    return true;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                ts.Complete();
            }
        }
    }

Am I missing anything?

Update: The stored procedure contains a simple insert statement:

INSERT INTO tblPurchaseOrder
(ShipmentID, CreateDate, CustomerNotes, PurchaseOrderState, PurchaseOrderNumber, LastActivityDate)
VALUES
(@ShipmentID, @CreateDate, @CustomerNotes, @PurchaseOrderState, @PurchaseOrderNumber, GETDATE());

--Step 2: return row that INSERTED to Client Computer.
SELECT dbo.viwGetPurchaseOrderWeight.Weight,* FROM tblPurchaseOrder LEFT OUTER JOIN viwGetPurchaseOrderWeight ON viwGetPurchaseOrderWeight.PurchaseOrderID =  tblPurchaseOrder.PurchaseOrderID WHERE (tblPurchaseOrder.PurchaseOrderID = Scope_Identity());

UPDATE2: When shutting down the MSDTC, the exception is thrown when the execution reached to this line:

using (SqlDataReader rdr = SqlHelper.ExecuteReader(/*Tr*/Cnn, "spPurchaseOrderAdd", purchaseOrderInfo.ExpectedShipment.ShipmentID, purchaseOrderInfo.CreateDate, purchaseOrderInfo.CustomerNotes, purchaseOrderInfo.Status, purchaseOrderInfo.PurchaseOrderNumber))

which means the subsequent lines has no effect.

@DavidHoerster I updated the question to include the procedure. — Nour
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. Otherwise it should stay local. — David Hoerster
@DavidHoerster that is exactly what confuses me!! there is no other connection! SqlHelper is a class from Microsoft.ApplicationBlocks.Data assembly, and I don't think it is opening a connection internally !! — Nour
It is easier getting answers if you post a stripped down code sample. Try to get rid of all the debug cruft. — usr

Crashman Crashman · Accepted Answer · 2013-04-23T20:57:57

Just be sure not open again the connection,

using (TransactionScope transactionScope = new TransactionScope()) {
    using (SqlConnection connection = new SqlConnection(connectionString)) {
    connection.Open();
    connection.Close();
    connection.Open(); // escalates to DTC
      }
}

Why is MSDTC involved?

4 Answers