469,578 Members | 1,210 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,578 developers. It's quick & easy.

BeginTransaction causes command to ignore timeout

Hello,
I had a chunk of code that would execute a stored proc to migrate data
between two systems (takes a few minutes) and then performs some data
manipulation on the migrated data. This was working fine when I was
using SqlCommand.CommandTimeout = 0.

After getting the basics to work I wanted to wrap the whole block in a
transaction to roll back everything if something went wrong:

Dim cnMigrate As New SqlConnection(pConnectionString)
Dim trMigrate As SqlTransaction = Nothing
Using cdMigrate As New SqlCommand("uspMigrate", cnMigrate)
With cdMigrate
Try
.CommandTimeout = 0
.CommandType = CommandType.StoredProcedure
.Connection.Open()
trMigrate = .Connection.BeginTransaction("Migrate")
.Transaction = trMigrate
.Parameters.AddWithValue("@MyParam", intMyValue)
Dim daResults As New SqlDataAdapter(cdMigrate)
Dim dsResults As New DataSet()
daResults.Fill(dsResults)
' Code to process post migrate data

' All done, commit the transaction
trMigrate.Commit()

Catch ex As Exception
If .Connection.State <ConnectionState.Closed AndAlso
trMigrate IsNot Nothing Then
trMigrate.Rollback()
End If
Throw
End Try
End With
End Using

Trouble is, the CommandTimeout property seems to be ignored now, and the
procedure times out after about a minute on .Fill

Do I have to set another timeout for the transaction? If so then where?
Aug 26 '08 #1
1 1808
Leon Mayne wrote:
Hello,
I had a chunk of code that would execute a stored proc to migrate data
between two systems (takes a few minutes) and then performs some data
manipulation on the migrated data. This was working fine when I was
using SqlCommand.CommandTimeout = 0.

After getting the basics to work I wanted to wrap the whole block in a
transaction to roll back everything if something went wrong:

Dim cnMigrate As New SqlConnection(pConnectionString)
Dim trMigrate As SqlTransaction = Nothing
Using cdMigrate As New SqlCommand("uspMigrate", cnMigrate)
With cdMigrate
Try
.CommandTimeout = 0
.CommandType = CommandType.StoredProcedure
.Connection.Open()
trMigrate = .Connection.BeginTransaction("Migrate")
.Transaction = trMigrate
.Parameters.AddWithValue("@MyParam", intMyValue)
Dim daResults As New SqlDataAdapter(cdMigrate)
Dim dsResults As New DataSet()
daResults.Fill(dsResults)
' Code to process post migrate data

' All done, commit the transaction
trMigrate.Commit()

Catch ex As Exception
If .Connection.State <ConnectionState.Closed AndAlso
trMigrate IsNot Nothing Then
trMigrate.Rollback()
End If
Throw
End Try
End With
End Using

Trouble is, the CommandTimeout property seems to be ignored now, and the
procedure times out after about a minute on .Fill

Do I have to set another timeout for the transaction? If so then where?
Nevermind, it's because the business layer calls use their own
instantiated connection to the database, which seems to hang if there is
another connection with an open transaction to the same database.
Aug 26 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Cath B | last post: by
3 posts views Thread by rh0dium | last post: by
2 posts views Thread by perspolis | last post: by
8 posts views Thread by Brian Tkatch | last post: by
20 posts views Thread by Mukesh | last post: by
2 posts views Thread by John Kotuby | last post: by
2 posts views Thread by Gil_H | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.