SQLClient - Nested Transactions. What are the possibilities?

Good day all..

I have a need to be able to handel nested transactions.  It seems that what I want is not easily attained.
I just about have what I need but...

Here's the situation...
        I have a routine the reads a .sql file
        it is possible that there may be nested SQL within in this fine.
              SOME SQL
               BEGIN TRANSACTION
                      SOME SQL
        In my current test environment I am able to execute the inner transaction with out any errors.
        I then execute the outer transaction, which is broken...  
        I try to execute the outer transaction... it fails... so I try to roll it back.  
        This is where the problem occures.  I seems that when I commit the inner transaction that the
        that the outer transaction gets lost.    Not a good explanation but perhaps the code will do a better job.

        Dim con As New SqlClient.SqlConnection(sbConnectionString.ToString)
        If Not con.State = ConnectionState.Open Then
        End If

        Dim cmd As SqlClient.SqlCommand = con.CreateCommand
        Dim trans As SqlTransaction
        trans = con.BeginTransaction
        cmd.Transaction = trans
        trans.save("Outer Trans")
        .CommandText = some SQL

        .CommandText = some SQL

        .CommandText = some BROKEN SQL

        .CommandText = some SQL

         trans.save("Inner Trans")

        .CommandText = some SQL

        .CommandText = some SQL

         trans.commit    ' Successfully commits

         trans.commit    '  This should fail...
           Trans.RollBack("Outer Trans")
                 ### THE PROBLEM OCCURES HERE###
                 An exception is thrown here saying the "Outer Trans"  Does not exist?
Perhaps I'm going about this all wrong...   Is there a better way to do this.  or do I tell the sender of these SQL files that we cannot except
nested transactions?

May thanks in advanced!


Who is Participating?
MADeonConnect With a Mentor Commented:
I believe that the transactions have to be given explicit names in order for nested transactions to work in SQL Server. Try:

              SOME SQL1
               BEGIN TRANSACTION Trans2
                      SOME SQL2
               COMMIT Trans2
        ROLLBACK Trans1

This should rollback both SQL1 and SQL2.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.