Exception at SqlCommand.Connection.Open() in nested TransactionScope

[Angela Yan]

Hi,

I have a VB class to update a MS SQL server according to different incoming
parameters in .Net 2.0. However, I keep getting the error message
"Communication with the underlying transaction manager has failed." when I
have nested TransactionScope. I am quite new to this topic, please kindly
correct me if my implementation is incorrect.


Public Class UpdateAssignment

MainFunction( param 1, param2)
Try

Using scope As TransactionScope = New TransactionScope()
.....
.....
Function2(param2)

scope.Complete()
End Using
Catch ex As System.Exception
IsSuccess = False
End Try
End Function

Function2
Try
Using scope As TransactionScope = New TransactionScope()

For i = 0 To 10
Function3
Next

scope.Complete()
End Using

Catch ex As System.Exception
IsSuccess = False
End Try
End Function

Function3
Dim oSqlConnection As SqlConnection = New
Data.SqlClient.SqlConnection
Dim oSqlCommand As SqlCommand = New Data.SqlClient.SqlCommand

Try
Using scope As TransactionScope = New TransactionScope()

oSqlConnection.ConnectionString = ConnectionString1
oSqlCommand.Connection = oSqlConnection
...'Add param here
...
oSqlCommand.Connection.Open()
oSqlCommand.ExecuteNonQuery()

scope.Complete()
End Using

Catch ex As System.Exception
IsSuccess = False
Finally
oSqlCommand.Dispose()
oSqlConnection.Dispose()
End Try
End Function
End


The exception will occur at the oSqlCommand.Connection.Open() in Function3
when it loops for the second time. The error message is "Communication with
the underlying transaction manager has failed." The inner exception is
"Error HRESULT E_FAIL has been returned from a call to a COM component."
Attached also the stack trace:


System.Transactions.TransactionManagerCommunicationException was caught
Message="Communication with the underlying transaction manager has
failed."
Source="System.Transactions"
StackTrace:
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 UpdateAssignment.Function3(TAJobAction JobAction) in
http://server//Project/App_Code/UpdateAssignment.vb:line 169

Any help is greatly appreciated.

Thank you.
Angela