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.SqlConnection(My.Settings.MyConnectionString)
cnn.Open()
Dim trx As SqlClient.SqlTransaction = cnn.BeginTransaction()

' 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.SqlConnection(My.Settings.GRPConnectionString)
m_cnn.Open()
m_cnnTransaction = m_cnn.BeginTransaction()

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

If (AllWentWell) Then
m_cnnTransaction.Commit
Else
m_cnnTransaction.Rollback
End If

'...

End Sub

 

[Rich P]

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

 

[BobRoyAce]

So, if sets of queries are good in and of themselves, to run in the
order I'm trying to run them, is there anything theoretically wrong
with doing nested transactions the way I am trying to do it? I was
creating two different connections to the DB, and two different
transactions, one after the other. Is that not allowed and/or will it
not accomplish what I'm after? For it to do what I want, even if
second transaction was committed, if first one is rolled back, the
second one would be too.