Transaction Promotion problem

microsoft.public.dotnet.framework.adonet

27 Jun 2006 6:02 PM

lestat.majere

We are using SQL Server 2005 with Visual Studio 2005.
Both the server and client are 2003 Server SP1 with MSDTC enabled
(Allow Inbound and Allow Outbound, Mutual Authentication Required).
Windows Firewall is disabled on the client.

We are trying to use the System.Transaction.TransactionScope object to
manage our transactions but it fails when a transaction needs to be
upgraded from simple to distributed. Here is a boiled down version of
the problem.

If I run the following program:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
namespace Test_Transactions
{
class Program
{
static void Main(string[] args)
{
   const string connString1 = "TimeOut=5;Data Source=CAPSDB02;Initial
Catalog=SCAPS_Central;Integrated Security=True";
   const string connString2 = "TimeOut=5;Data Source=CAPSDB02;Initial
Catalog=SCAPS_dev;Integrated Security=True";
   // This creates a transaction (new to .NET 2.0). Every SQL
connection will enlist to it.
   using (TransactionScope scope = new TransactionScope(
    TransactionScopeOption.Required,
    TimeSpan.FromSeconds(10)))
   {
    // Create two connections, not opening them yet: nothing happens
    SqlConnection conn1 = new SqlConnection(connString1);
    SqlConnection conn2 = new SqlConnection(connString2);
    try
    {
     Console.WriteLine("Connection 1");
     // Open the first one: fine
     conn1.Open();
     Console.WriteLine("Connection 2");
     // Open the second one: it tries to promote the first one to a
distributed one first
     conn2.Open();
     Console.WriteLine("Success");
    }
    catch (Exception e)
    {
     Console.WriteLine("Exception: " + e.Message);
     Console.WriteLine("Inner Exception: " + e.Message);
     Console.WriteLine("Inner Stack trace: " + e.StackTrace);
    }
    finally
    {
     conn1.Dispose();
     conn2.Dispose();
    }
    scope.Complete();
   }
}
}
}

I get the following output:

Connection 1
Connection 2
Exception: Communication with the underlying transaction manager has
failed.
Inner Exception: Communication with the underlying transaction manager
has fail
ed.
Inner Stack trace:     at
System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[]
propagationToken)
   at
System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction
tx)
   at
System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction
tx)
   at System.Transactions.EnlistableStates.Promote(InternalTransaction
tx)
   at System.Transactions.Transaction.Promote()
   at
System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction
transaction)
   at
System.Transactions.TransactionInterop.GetExportCookie(Transaction
transaction, Byte[] whereabouts)
   at
System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction
tx)
   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction
tx)
   at
System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction
transaction)
   at
System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction
transaction)
   at
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
   at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
   at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Test_Transactions.Program.Main(String[] args) in C:\Documents and
Settings
\vlauzon\My Documents\Visual Studio
2005\Projects\Test_Transactions\Test_Transac
tions\Program.cs:line 31

As you can see, the first connection is opened without a problem while
the second one (allegedly forcing a promotion of the transaction from
simple to distributed) is unable to.

This problem doesn't happened if this program is run on the same box
than the SQL Server. Hence I suspect a problem with the coordination
between the DTCs.

Thank you for your help,

Martin

27 Jun 2006 6:33 PM

Sahil Malik [MVP C#]

Is MSDTC running?
Is it blocked by firewalls?

- Sahil Malik [MVP]
http://blah.winsmarts.com

<lestat.maj***@gmail.com> wrote in message
Show quote

news:1151431322.401796.83550@p79g2000cwp.googlegroups.com...
> We are using SQL Server 2005 with Visual Studio 2005.
> Both the server and client are 2003 Server SP1 with MSDTC enabled
> (Allow Inbound and Allow Outbound, Mutual Authentication Required).
> Windows Firewall is disabled on the client.
>
> We are trying to use the System.Transaction.TransactionScope object to
> manage our transactions but it fails when a transaction needs to be
> upgraded from simple to distributed. Here is a boiled down version of
> the problem.
>
> If I run the following program:
>
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Transactions;
> namespace Test_Transactions
> {
> class Program
> {
> static void Main(string[] args)
> {
>   const string connString1 = "TimeOut=5;Data Source=CAPSDB02;Initial
> Catalog=SCAPS_Central;Integrated Security=True";
>   const string connString2 = "TimeOut=5;Data Source=CAPSDB02;Initial
> Catalog=SCAPS_dev;Integrated Security=True";
>   // This creates a transaction (new to .NET 2.0). Every SQL
> connection will enlist to it.
>   using (TransactionScope scope = new TransactionScope(
>    TransactionScopeOption.Required,
>    TimeSpan.FromSeconds(10)))
>   {
>    // Create two connections, not opening them yet: nothing happens
>    SqlConnection conn1 = new SqlConnection(connString1);
>    SqlConnection conn2 = new SqlConnection(connString2);
>    try
>    {
>     Console.WriteLine("Connection 1");
>     // Open the first one: fine
>     conn1.Open();
>     Console.WriteLine("Connection 2");
>     // Open the second one: it tries to promote the first one to a
> distributed one first
>     conn2.Open();
>     Console.WriteLine("Success");
>    }
>    catch (Exception e)
>    {
>     Console.WriteLine("Exception: " + e.Message);
>     Console.WriteLine("Inner Exception: " + e.Message);
>     Console.WriteLine("Inner Stack trace: " + e.StackTrace);
>    }
>    finally
>    {
>     conn1.Dispose();
>     conn2.Dispose();
>    }
>    scope.Complete();
>   }
> }
> }
> }
>
> I get the following output:
>
> Connection 1
> Connection 2
> Exception: Communication with the underlying transaction manager has
> failed.
> Inner Exception: Communication with the underlying transaction manager
> has fail
> ed.
> Inner Stack trace:     at
> System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[]
> propagationToken)
>   at
> System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction
> tx)
>   at
> System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction
> tx)
>   at System.Transactions.EnlistableStates.Promote(InternalTransaction
> tx)
>   at System.Transactions.Transaction.Promote()
>   at
> System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction
> transaction)
>   at
> System.Transactions.TransactionInterop.GetExportCookie(Transaction
> transaction, Byte[] whereabouts)
>   at
> System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction
> tx)
>   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction
> tx)
>   at
> System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction
> transaction)
>   at
> System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction
> transaction)
>   at
> System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
> owningObject)
>   at
> System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> owningConnection)
>   at
> System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> outerConnection, DbConnectionFactory connectionFactory)
>   at System.Data.SqlClient.SqlConnection.Open()
>   at Test_Transactions.Program.Main(String[] args) in C:\Documents and
> Settings
> \vlauzon\My Documents\Visual Studio
> 2005\Projects\Test_Transactions\Test_Transac
> tions\Program.cs:line 31
>
> As you can see, the first connection is opened without a problem while
> the second one (allegedly forcing a promotion of the transaction from
> simple to distributed) is unable to.
>
> This problem doesn't happened if this program is run on the same box
> than the SQL Server. Hence I suspect a problem with the coordination
> between the DTCs.
>
>
> Thank you for your help,
>
> Martin
>

27 Jun 2006 6:50 PM

lestat.majere

Yes, MS DTC is running on both server and client.

Both Firewalls are not activated.

Martin

Sahil Malik [MVP C#] wrote:
Show quote

> Is MSDTC running?
> Is it blocked by firewalls?
>
> - Sahil Malik [MVP]
> http://blah.winsmarts.com
>
>
>
>
> <lestat.maj***@gmail.com> wrote in message
> news:1151431322.401796.83550@p79g2000cwp.googlegroups.com...
> > We are using SQL Server 2005 with Visual Studio 2005.
> > Both the server and client are 2003 Server SP1 with MSDTC enabled
> > (Allow Inbound and Allow Outbound, Mutual Authentication Required).
> > Windows Firewall is disabled on the client.
> >
> > We are trying to use the System.Transaction.TransactionScope object to
> > manage our transactions but it fails when a transaction needs to be
> > upgraded from simple to distributed. Here is a boiled down version of
> > the problem.
> >
> > If I run the following program:
> >
> > using System;
> > using System.Data;
> > using System.Data.SqlClient;
> > using System.Transactions;
> > namespace Test_Transactions
> > {
> > class Program
> > {
> > static void Main(string[] args)
> > {
> >   const string connString1 = "TimeOut=5;Data Source=CAPSDB02;Initial
> > Catalog=SCAPS_Central;Integrated Security=True";
> >   const string connString2 = "TimeOut=5;Data Source=CAPSDB02;Initial
> > Catalog=SCAPS_dev;Integrated Security=True";
> >   // This creates a transaction (new to .NET 2.0). Every SQL
> > connection will enlist to it.
> >   using (TransactionScope scope = new TransactionScope(
> >    TransactionScopeOption.Required,
> >    TimeSpan.FromSeconds(10)))
> >   {
> >    // Create two connections, not opening them yet: nothing happens
> >    SqlConnection conn1 = new SqlConnection(connString1);
> >    SqlConnection conn2 = new SqlConnection(connString2);
> >    try
> >    {
> >     Console.WriteLine("Connection 1");
> >     // Open the first one: fine
> >     conn1.Open();
> >     Console.WriteLine("Connection 2");
> >     // Open the second one: it tries to promote the first one to a
> > distributed one first
> >     conn2.Open();
> >     Console.WriteLine("Success");
> >    }
> >    catch (Exception e)
> >    {
> >     Console.WriteLine("Exception: " + e.Message);
> >     Console.WriteLine("Inner Exception: " + e.Message);
> >     Console.WriteLine("Inner Stack trace: " + e.StackTrace);
> >    }
> >    finally
> >    {
> >     conn1.Dispose();
> >     conn2.Dispose();
> >    }
> >    scope.Complete();
> >   }
> > }
> > }
> > }
> >
> > I get the following output:
> >
> > Connection 1
> > Connection 2
> > Exception: Communication with the underlying transaction manager has
> > failed.
> > Inner Exception: Communication with the underlying transaction manager
> > has fail
> > ed.
> > Inner Stack trace:     at
> > System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[]
> > propagationToken)
> >   at
> > System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction
> > tx)
> >   at
> > System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction
> > tx)
> >   at System.Transactions.EnlistableStates.Promote(InternalTransaction
> > tx)
> >   at System.Transactions.Transaction.Promote()
> >   at
> > System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction
> > transaction)
> >   at
> > System.Transactions.TransactionInterop.GetExportCookie(Transaction
> > transaction, Byte[] whereabouts)
> >   at
> > System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction
> > tx)
> >   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction
> > tx)
> >   at
> > System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction
> > transaction)
> >   at
> > System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction
> > transaction)
> >   at
> > System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
> > owningObject)
> >   at
> > System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> > owningConnection)
> >   at
> > System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> > outerConnection, DbConnectionFactory connectionFactory)
> >   at System.Data.SqlClient.SqlConnection.Open()
> >   at Test_Transactions.Program.Main(String[] args) in C:\Documents and
> > Settings
> > \vlauzon\My Documents\Visual Studio
> > 2005\Projects\Test_Transactions\Test_Transac
> > tions\Program.cs:line 31
> >
> > As you can see, the first connection is opened without a problem while
> > the second one (allegedly forcing a promotion of the transaction from
> > simple to distributed) is unable to.
> >
> > This problem doesn't happened if this program is run on the same box
> > than the SQL Server. Hence I suspect a problem with the coordination
> > between the DTCs.
> >
> >
> > Thank you for your help,
> >
> > Martin
> >

27 Jun 2006 7:57 PM

Sahil Malik [MVP C#]

Hmm .. since the problem doesn't occur locally, it is definitely a network
issue. But since you say that both MSDTC's are running and both firewalls
are turned off, I'm out of ideas :-/

But I think it is a network issue; here is a relevant article -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndcom/html/msdn_dcomfirewall.asp

SM

<lestat.maj***@gmail.com> wrote in message
Show quote

news:1151434208.003420.22060@y41g2000cwy.googlegroups.com...
> Yes, MS DTC is running on both server and client.
>
> Both Firewalls are not activated.
>
>
>
> Martin
>
> Sahil Malik [MVP C#] wrote:
>> Is MSDTC running?
>> Is it blocked by firewalls?
>>
>> - Sahil Malik [MVP]
>> http://blah.winsmarts.com
>>
>>
>>
>>
>> <lestat.maj***@gmail.com> wrote in message
>> news:1151431322.401796.83550@p79g2000cwp.googlegroups.com...
>> > We are using SQL Server 2005 with Visual Studio 2005.
>> > Both the server and client are 2003 Server SP1 with MSDTC enabled
>> > (Allow Inbound and Allow Outbound, Mutual Authentication Required).
>> > Windows Firewall is disabled on the client.
>> >
>> > We are trying to use the System.Transaction.TransactionScope object to
>> > manage our transactions but it fails when a transaction needs to be
>> > upgraded from simple to distributed. Here is a boiled down version of
>> > the problem.
>> >
>> > If I run the following program:
>> >
>> > using System;
>> > using System.Data;
>> > using System.Data.SqlClient;
>> > using System.Transactions;
>> > namespace Test_Transactions
>> > {
>> > class Program
>> > {
>> > static void Main(string[] args)
>> > {
>> >   const string connString1 = "TimeOut=5;Data Source=CAPSDB02;Initial
>> > Catalog=SCAPS_Central;Integrated Security=True";
>> >   const string connString2 = "TimeOut=5;Data Source=CAPSDB02;Initial
>> > Catalog=SCAPS_dev;Integrated Security=True";
>> >   // This creates a transaction (new to .NET 2.0). Every SQL
>> > connection will enlist to it.
>> >   using (TransactionScope scope = new TransactionScope(
>> >    TransactionScopeOption.Required,
>> >    TimeSpan.FromSeconds(10)))
>> >   {
>> >    // Create two connections, not opening them yet: nothing happens
>> >    SqlConnection conn1 = new SqlConnection(connString1);
>> >    SqlConnection conn2 = new SqlConnection(connString2);
>> >    try
>> >    {
>> >     Console.WriteLine("Connection 1");
>> >     // Open the first one: fine
>> >     conn1.Open();
>> >     Console.WriteLine("Connection 2");
>> >     // Open the second one: it tries to promote the first one to a
>> > distributed one first
>> >     conn2.Open();
>> >     Console.WriteLine("Success");
>> >    }
>> >    catch (Exception e)
>> >    {
>> >     Console.WriteLine("Exception: " + e.Message);
>> >     Console.WriteLine("Inner Exception: " + e.Message);
>> >     Console.WriteLine("Inner Stack trace: " + e.StackTrace);
>> >    }
>> >    finally
>> >    {
>> >     conn1.Dispose();
>> >     conn2.Dispose();
>> >    }
>> >    scope.Complete();
>> >   }
>> > }
>> > }
>> > }
>> >
>> > I get the following output:
>> >
>> > Connection 1
>> > Connection 2
>> > Exception: Communication with the underlying transaction manager has
>> > failed.
>> > Inner Exception: Communication with the underlying transaction manager
>> > has fail
>> > ed.
>> > Inner Stack trace:     at
>> > System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[]
>> > propagationToken)
>> >   at
>> > System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction
>> > tx)
>> >   at
>> > System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction
>> > tx)
>> >   at System.Transactions.EnlistableStates.Promote(InternalTransaction
>> > tx)
>> >   at System.Transactions.Transaction.Promote()
>> >   at
>> > System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction
>> > transaction)
>> >   at
>> > System.Transactions.TransactionInterop.GetExportCookie(Transaction
>> > transaction, Byte[] whereabouts)
>> >   at
>> > System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction
>> > tx)
>> >   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction
>> > tx)
>> >   at
>> > System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction
>> > transaction)
>> >   at
>> > System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction
>> > transaction)
>> >   at
>> > System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
>> > owningObject)
>> >   at
>> > System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
>> > owningConnection)
>> >   at
>> > System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
>> > outerConnection, DbConnectionFactory connectionFactory)
>> >   at System.Data.SqlClient.SqlConnection.Open()
>> >   at Test_Transactions.Program.Main(String[] args) in C:\Documents and
>> > Settings
>> > \vlauzon\My Documents\Visual Studio
>> > 2005\Projects\Test_Transactions\Test_Transac
>> > tions\Program.cs:line 31
>> >
>> > As you can see, the first connection is opened without a problem while
>> > the second one (allegedly forcing a promotion of the transaction from
>> > simple to distributed) is unable to.
>> >
>> > This problem doesn't happened if this program is run on the same box
>> > than the SQL Server. Hence I suspect a problem with the coordination
>> > between the DTCs.
>> >
>> >
>> > Thank you for your help,
>> >
>> > Martin
>> >
>