promote a transaction to DTC

promote a transaction to DTC

• KevlarMpowered

  Because I got some code that runs flawlessly on Oracle and MS SQL Server 2000, but bombs out on SQL Server 2005 because the transaction logic tries to be too smart and fails doing so.

  The situation is that object A is opening a SQL connection and a reader while reading it comes to a situation where it creates object B which opens another connection to another database. On SQL Server 2000 and Oracle that works flawlessly, in my understanding because the transaction is already a distributed transaction. With SQL Server 2005 there seems to be some (in this case failing) optimization that tries to keep the transaction local if it does not need to be distributed, but it tries to promote it of course when it detects the second database connection trying to be established. That fails because the transaction cannot be promoted while a reader is open on the first one. So a preemtive promotion seems to be the best without breaking the code.

  Little code example to reproduce the exception in case somebody is interested ;)

  using (TransactionScope transaction = new TransactionScope())
  {
    System.Data.Common.DbConnection connection1 = providerFactory.CreateConnection();
    connection1.ConnectionString = connString;
    connection1.Open();
    System.Data.Common.DbCommand cmd = connection1.CreateCommand();
    cmd.CommandText = cmdText;
    System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
    System.Data.Common.DbConnection connection2 = providerFactory.CreateConnection();
    connection2.ConnectionString = connString;
    connection2.Open();
  }

  Btw. your line of code would fix the problem, thanks... will see how we solve it. For query only commands we put in now an "Enlist=false" for the SQL Server 2005, if it optimizes the transaction handling it should probably go that step further and analyse the command, too, to see if promoting is necessary.

  
  

• susie

  Hi John,

  You are the first to ask this question. Interesting, why do you want to do that The transaction will promote when it will need by itself.

  But if you just want to do some testing, the simplest way to promote is by calling:

  TransactionInterop.GetTransmitterPropagationToken(tx);

  There are other things that can trigger promotion - see http://blogs.msdn.com/florinlazar/archive/2005/05/12/416805.aspx for more details.

  
  
  
• promote a transaction to DTC