HOWTO: Implement Nested Transactions with ADO and SQL Server

• ActiveX Data Objects (ADO), versions 2.0, 2.01, 2.1, 2.1 SP1, 2.1 SP2
• Microsoft SQL Server versions 6.5, 7.0

SUMMARY

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

Model-2: Using save points:

MORE INFORMATION

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 the following reference to the project: "Microsoft ActiveX Data Objects 2.1 Library."



3. Paste the following lines into the Code window for Form1:




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')"
   
  ' Uncommit 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=sa;Data " & _
           "Source=<insert your SQL Server>;database=pubs"
  Cn.CursorLocation = adUseClient
End Sub 

4. Click on the NestedTrans command. This commits all nested transactions.



5. Click on SavePoint command. This rolls back InnerTrans2 and InnerTrans3, getting the following results:




101 1st Store
102 2nd Store
103 3rd Store
104 4th Store

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Ammar Abuthuraya, Microsoft Corporation

REFERENCES

For additional information, please click the article numbers below to view the articles in the Microsoft Knowledge Base:

Q177138 INFO: Nested Transactions Not Available in ODBC/OLE DB/ADO

Q224071 INFO: Understanding ADO Transactions with MS SQL Server

Additional query words:

Keywords : kbADO kbADO200 kbADO201 kbADO210 kbOLEDB kbSQLServ kbSQLServ650 kbSQLServ700 kbGrpVBDB kbDSupport kbADO210sp2
Version : WINDOWS:2.0,2.01,2.1,2.1 SP1,2.1 SP2; winnt:6.5,7.0
Platform : WINDOWS winnt
Issue type : kbhowto