Microsoft KB Archive/238163

= How To Implement Nested Transactions with ADO and SQL Server =

Article ID: 238163

Article Last Modified on 7/1/2004

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.01
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q238163



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
- 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.



MORE INFORMATION
The following is a sample Visual Basic ADO application that goes against SQL Server. It uses the "stores" table in pubs database.  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

 Add a reference to the Microsoft ActiveX Data Objects Library.  Paste the following lines into the Code window for Form1:

Note You must change 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=;Data " & _ "Source=;database=pubs" Cn.CursorLocation = adUseClient End Sub  Change the connection string in the Form_Load method appropriately to connect to your SQL Server. Run the project, and click the NestedTrans command. This commits all nested transactions.</li> Click SavePoint. This rolls back InnerTrans2 and InnerTrans3, yielding the following results:

101 1st Store

102 2nd Store

103 3rd Store

104 4th Store

</li></ol>

<div class="references_section">