CODEWITHSUNDEEP
t-sql.net-3.5transactionscope
DDiVita
DDiVita

Reputation: 4265

Inner Transactionscope error: "Communication with the underlying transaction manager has failed"

I am not sure I am doing any of this correctly. Is it because I am opening two connections? I am closing them regardless of errors. I did try putting in some inner transaction scopes and setting the second one to RequiresNew. The two methods are independent of each other, however, if one fails I need them both to rollback. I may have to modify how I am creating and closing the connections, I feel. Any thoughts? Here is some example code of what I am doing:

Public Sub TransMethod()
    Using sTran As New Transactions.TransactionScope
        factory1.UpdateMethod(someObject1)
        facotry2.insert(someobject2)
        sTran.Complete()
    End Using
End Sub

Public Class factory1
    Public Shared Sub UpdateMethod(obj)
        dim someSQLParams....
        DataAcces.ExecuteNonQuery(command,someSQLParams)
    End Sub
End Class

Public Class factory2
    Public Shared Sub Insert(obj)
        dim someSQLParams....
        DataAcces.ExecuteNonQuery(command,someSQLParams)
    End Sub
End Class

Public Function ExecuteNonQuery(ByVal spname As String, _
        ByVal ParamArray parameterValues() As Object) As Object
    Dim connection As SqlConnection = Nothing            
    Dim command As SqlCommand = Nothing
    Dim res As Object = Nothing
    Try
        connection = New SqlConnection(_connectionString)
        command = New SqlCommand(spname, connection)
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.AddRange(parameterValues)
        connection.Open()
        command.ExecuteNonQuery()
        res = command.Parameters(command.Parameters.Count - 1).Value
    Catch ex As Exception
        CreateDataEntry(ex, WriteType.ToFile, spname)
        If Not (transaction Is Nothing) Then
            transaction.Rollback()
        End If
    Finally
        If Not (connection Is Nothing) AndAlso _
            (connection.State = ConnectionState.Open) Then _
                connection.Close()
        If Not (command Is Nothing) Then command.Dispose()
    End Try
    Return res
End Function

Upvotes: 0

Views: 2186

Answers (1)

eglasius
eglasius

Reputation: 36027

Whenever you open more than 1 connection using TransactionScope, it changes from a normal transaction against sql server to a distributed transaction, which requires setting up the MSDTC.

That will happen even if the connections have the same connection string, which is one of the "by design" issues. I haven't followed up if it remains the same on .net 3.5+

Upvotes: 1

Related Questions