L
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
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