tags:

views:

88

answers:

0

Hi,

I have a ServicedComponent to a distributed transaction.

Within this class a I have a method SyncRow and I am opening a new Sql connection wrapped in a TransactionScope. I am calling a sql stored proc to update data.

But I am getting a Timeout on the statement objCmd.ExecuteNonQuery.

Any ideas why?

Public Function SyncRow(ByVal ID As Integer) As Boolean

        Dim objCmd As SqlClient.SqlCommand
        Dim objPar As SqlClient.SqlParameter

        Try
            Dim options As New TransactionOptions()
            Dim span As New TimeSpan(0, 0, 10)
            options.IsolationLevel = IsolationLevel.ReadCommitted
            options.Timeout = span

            Using transScope = New TransactionScope(TransactionScopeOption.RequiresNew, options, EnterpriseServicesInteropOption.None)
                Using objConn = New SqlClient.SqlConnection(WebConnstr)
                    objConn.Open()
                    objCmd = New SqlClient.SqlCommand
                    objCmd.Connection = objConn

                    objPar = New SqlClient.SqlParameter
                    objPar.ParameterName = "@cciID"
                    objPar.Value = ID
                    objCmd.Parameters.Add(objPar)

                    objPar = New SqlClient.SqlParameter
                    objPar.ParameterName = "@Synchronised"
                    objPar.Value = True
                    objCmd.Parameters.Add(objPar)

                    objCmd.CommandType = CommandType.StoredProcedure
                    objCmd.CommandText = "sp_syncCapturedClientInfo"

                    If objCmd.ExecuteNonQuery > 0 Then
                        Return True
                    Else
                        Return False
                    End If

                End Using
                transScope.Complete()
             End Using


        Catch ex As Exception
            WriteLog("Sync CapturedClientInfo failed - error in SyncRow." & vbCrLf & ex.ToString())
            Return False
        End Try

    End Function