Nesting SQL Server 2005 transactions in VB.NET

[BobRoyAce]

I have a situation where I have one procedure that executes a bunch of
database code in a transaction. In this procedure, in the middle of
the aforementioned transaction, I call another procedure that does a
series of things in yet another transaction. For some reason, after I
come back from the second procedure, where that transaction is
committed, and I try to run a simple SELECT SUM(*)... query in the
first one, it just times out. So, I am thinking it may be being caused
by the transaction stuff. My question relates to understanding the
proper way to code transactions within transactions. In case it's
relevant, these procedures are in different places (i.e. one in a
class, another in a form's code). Any insights would be greatly
appreciated.

A simplified version of what I have is something like follows:

Private Sub FirstProc()
Dim cnn As New
SqlClient.SqlCo nnection(My.Set tings.MyConnect ionString)
cnn.Open()
Dim trx As SqlClient.SqlTr ansaction = cnn.BeginTransa ction()

' Execute first command query
' Execute second command query
' Execute third command query

SecondProc()

' Try to execute SELECT SUM... FAILS with timeout

' Execute fourth command query
If (AllWentWell) Then
trx.Commit
Else
trx.Rollback
End If

'...

End Sub

Private Sub SecondProc()
m_cnn = New SqlClient.SqlCo nnection(My.Set tings.GRPConnec tionString)
m_cnn.Open()
m_cnnTransactio n = m_cnn.BeginTran saction()

' Execute first command query
' Execute second command query
' Execute third command query

If (AllWentWell) Then
m_cnnTransactio n.Commit
Else
m_cnnTransactio n.Rollback
End If

'...

End Sub

[Rich P]

Re: Nesting SQL Server 2005 transactions in VB.NET

I would set up a test system (test DB) and then try executing your
queries in a stored procedure from Query Analyzer. If the SP works in
Query Analyzer then the query statements are OK and should work from the
sqlClient. But I will wager that you will encounter the same problem
using the SP that you are having with the sqlClient -- in which case you
need to modify the sql code.

If, however, the SP in query analyzer works fine, then I would consider
using a sqlDataAdapter instead of trying to run your transaction
directly from the sqlClient. You could run the SP directly from the
sqlDataAdapter. SelectCommand.

Rich

*** Sent via Developersdex http://www.developersdex.com ***