we track an unpleasant timeout error in one of our applications.
I wanted to know that (if any) differences in the use of SQL transactions in an application compared to writing to a stored procedure using the TSQL statement. We will need to rebuild the stored procs and vb code to make it work, and I'm not sure if it will be worth the effort at this time.
Public Sub RetrieveTData(ByVal cID As String, ByVal cnn As SqlConnection) As Boolean
Dim sqlTran As SqlTransaction
cnn.Open()
sqlTran = cnn.BeginTransaction
Try
If Not DataAlreadyTransferred(cID) Then
Dim Command As New SqlCommand("usp_BigNasty_CopyDataFromDB1toDB2")
Command.CommandType = CommandType.StoredProcedure
Command.Transaction = sqlTran
Command.Parameters.AddWithValue("@cID", cID)
Command.Connection = cnn
Command.ExecuteNonQuery()
sqlTran.Commit()
Endif
Catch ex As Exception
sqlTran.Rollback()
Throw
Finally
cnn.Close()
End Try
End Sub
We are not sure if the timeout occurs in DataAlreadyTransferred()or usp_BigNasty_CopyDataFromDB1toDB2because of the way try / catch is written. We can restructure this code, but it will take about a week or so to get it into production (today there are no errors in test / dev)
DB1 - , ,
DB2 - , -
DataAlreadyTransferred(cID) , DB2 - , DB2 , ( DB1, ). DB2 , .
usp_BigNasty_CopyDataFromDB1toDB2 20-30 DB1 DB2, , -
, , ...
, , . procs, , /-, . ..
.