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
This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.
Showing posts with label Transactionscope. Show all posts
Showing posts with label Transactionscope. Show all posts
Wednesday, March 21, 2012
Subscribe to:
Posts (Atom)