How To Implement Nested Transactions with ADO and SQL Server

ADO, with both SQLOLEDB and MSDASQL providers, does not support nested transactions with SQL Server. However, native T-SQL commands can still be used with ADO to implement nested transactions against SQL Server.

This can be accomplished in two different ways:

Model-1: Without using save points

- Begin OuterTrans
- Begin InnerTrans1
- Begin InnerTrans2
- Begin InnerTrans3

Analysis:
Committing OuterTrans commits all nested transactions. Rolling back OuterTrans rolls back all nested transactions.

Model-2: Using save points:

- Begin OuterTrans
- Save InnerTrans1
- Save InnerTrans2
- Save InnerTrans3

Analysis:
Committing OuterTrans commits all nested transactions. Rolling back any saved inner-transaction point rolls back all transactions nested under that point. Saved points can be used to roll back nested transactions from a given point on without affecting transactions nested under different saved points.

The following is a sample Visual Basic ADO application that goes against SQL Server. It uses the "stores" table in pubs database.

1. Create a new standard EXE Visual Basic project. Form1 is created by default. Add two command buttons to Form1 as follows:
   Command1.Caption : Use Nested Transactions
   Command1.Name : NestedTrans
   
   Command2.Caption : Use Save Points
   Command2.Name : SavePoint
2. Add a reference to the Microsoft ActiveX Data Objects Library.
3. Paste the following lines into the Code window for Form1:
   
   Note You must change User ID=<User ID> to the correct value before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

   Option Explicit
   Dim Cn As New ADODB.Connection
   Dim Cmd As New ADODB.Command
   Dim rst As New ADODB.Recordset
   
   Private Sub SavePoint_Click()
    
     With rst
       .ActiveConnection = Cn
       .CursorType = adOpenStatic
       .Source = "select * from stores where stor_id LIKE '10%'"
       .Open
     End With
    
     Cn.Execute "Delete from stores where stor_id LIKE  '10%'"
   
     ' OuterTrans
     Cn.Execute "BEGIN TRANSACTION OuterMost"
   
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(101,'1st Store')"
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(102,'2nd Store')"
     
     ' InnerTrans1 Transaction
     Cn.Execute "SAVE TRANSACTION InnerTrans1"
    
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(103,'3rd Store')"
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(104,'4th Store')"
     
     ' InnerTrans2 Transaction
     Cn.Execute "SAVE TRANSACTION InnerTrans2"
      
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(105,'5th Store')"
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(106,'6th Store')"
      
     ' InnerTrans3 Transaction
     Cn.Execute "SAVE TRANSACTION InnerTrans3"
     
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(107,'7th Store')"
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(108,'8th Store')"
      
     Cn.Execute "ROLLBACK TRANSACTION InnerTrans2"
     Cn.Execute "COMMIT TRANSACTION OuterMost"
     
     rst.Requery
    
     While Not rst.EOF
       Debug.Print rst(0), rst(1)
       rst.MoveNext
     Wend
     
     rst.Close
   End Sub
   
   Private Sub NestedTrans_Click()
    
     With rst
       .ActiveConnection = Cn
       .CursorType = adOpenStatic
       .Source = "select * from stores where stor_id LIKE '10%'"
       .Open
     End With
    
     Cn.Execute "Delete from stores where stor_id LIKE  '10%'"
     
     ' OuterTrans
     Cn.Execute "BEGIN TRANSACTION OuterMost"
   
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(101,'1st Store')"
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(102,'2nd Store')"
     
     ' InnerTrans1 Transaction
      Cn.Execute "BEGIN TRANSACTION InnerTrans1"
    
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(103,'3rd Store')"
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(104,'4th Store')"
     
     ' InnerTrans2 Transaction
     Cn.Execute "BEGIN TRANSACTION InnerTrans2"
     
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(105,'5th Store')"
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(106,'6th Store')"
      
     ' InnerTrans3 Transaction
     Cn.Execute "BEGIN TRANSACTION InnerTrans3"
     
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(107,'7th Store')"
     Cn.Execute "Insert INTO Stores (stor_id, stor_name) " & _
                "VALUES(108,'8th Store')"
      
     ' Uncomment the following line to roll back the OuterTrans that rolls
     ' all nested transactions. 
     ' Cn.Execute "ROLLBACK TRANSACTION OuterMost"
   
     ' The following commits all nested transactions.
     Cn.Execute "COMMIT TRANSACTION OuterMost"
     
     rst.Requery
    
     While Not rst.EOF
       Debug.Print rst(0), rst(1)
       rst.MoveNext
     Wend
     
     rst.Close
   End Sub
   
   Private Sub Form_Load()
      Cn.Open "Provider=SQLOLEDB;User ID=<User ID>;Data " & _
              "Source=<insert your SQL Server>;database=pubs"
     Cn.CursorLocation = adUseClient
   End Sub
   					

4. Change the connection string in the Form_Load method appropriately to connect to your SQL Server.
5. Run the project, and click the NestedTrans command. This commits all nested transactions.
6. Click SavePoint. This rolls back InnerTrans2 and InnerTrans3, yielding the following results:
   101 1st Store
   102 2nd Store
   103 3rd Store
   104 4th Store

For additional information, please click the article numbers below to view the articles in the Microsoft Knowledge Base: SQL Server Books on Line, documentation.