Showing posts with label Transactionscope. Show all posts
Showing posts with label Transactionscope. Show all posts

Wednesday, March 21, 2012

MSDTC error while using Transactionscope

In one of my project while writing a client application in VB.NET I had to make a call to two different databases on different servers and in case of error encountered both the transaction had to roll back. I used transactionscope to achieve this. I could use this since the database server is SQL 2008. Transactionscope ( a very cool thing) can be used on in SQL 2005 or higher. However I was running into the following error whenever while trying to open connection2 ( second conection ).

Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.



Here is a snippet of my code (in VB.NET)


While debugging through the code, it thre error as soon as it was trying to open the oConn2 ( server2)
I checked in the database server if DTC was enabled. It was. What I had missed was the MSDTC had to be configured in the client server as well. Once the MSDTC was configured on the client server Viola it worked like a charm.




Imports System.Transactions  'you will have to add reference to System.Transactions first

Using scope As New TransactionScope()
Using oconn1 As New SqlConnection(strconn1)


    ocmd1 = New SqlClient.SqlCommand()
    ProgressBar1.PerformStep()
    ocmd1.Connection = oconn1
    ocmd1.CommandText = "PROC_SKIPTRACE_RESULTS_INSERT"
    ocmd1.CommandType = CommandType.StoredProcedure
    ocmd1.CommandTimeout = 600
    Dim paramPTNO As New SqlParameter("@ptno", Convert.ToInt32(newitems(0).ToString))
    paramPTNO.Direction = ParameterDirection.Input
    ocmd1.Parameters.Add(paramPTNO)
   Try
   If oconn1.State = ConnectionState.Closed Then


       oconn1.Open()
   End If
   returnValue = ocmd1.ExecuteNonQuery()
  Catch ex As Exception
    MsgBox(ex.Message.ToString())
  Finally
    oconn1.Close()
  End Try


Using oConn2 As New SqlConnection(strconn2)
 ocmd2 = New SqlClient.SqlCommand()
 ocmd2.Connection = oConn2
 ocmd2.CommandText = "PROC_SKIPTRACE_PATIENT_TABLE_SUSPENSE_UPDATE"
 ocmd2.CommandType = CommandType.StoredProcedure
 ocmd2.CommandTimeout = 600
 Dim paramintPTNO As New SqlParameter("@ptno", Convert.ToInt32(newitems(0).ToString))
 paramintPTNO.Direction = ParameterDirection.Input
 ocmd2.Parameters.Add(paramintPTNO)


Try
 If oConn2.State = ConnectionState.Closed Then
 oConn2.Open()
 End If
 returnValue = ocmd2.ExecuteNonQuery()
Catch ex As Exception
 MsgBox(ex.Message.ToString())
Finally
 oConn2.Close()
End Try


End Using 'oConn2
End Using 'oConn1
scope.Complete()
End Using ' transactionScope