Microsoft KB Archive/198024

= INFO: Understanding ADO Transactions with MS SQL Server =

Article ID: 198024

Article Last Modified on 10/31/2003

-

APPLIES TO


 * Microsoft OLE DB Provider for SQL Server 7.0
 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7

-



This article was previously published under Q198024



SUMMARY
The ActiveX Data Objects (ADO) Connection method BeginTrans states that it begins a new Transaction, but it executes "set implicit_transactions on." In many cases, "set implicit_transactions on" is equivalent to T-SQL BEGIN TRANS. However, there are subtle differences. This article illustrates one of those differences.



How Does ADO Behave with Respect to Transactions
By default ADO operates in AutoCommit mode, unless you start a implicit transaction by executing Connection.BeginTrans.

Implicit_transactions begin a transaction on the server for each statement, and commits do not occur until they are manually issued.

So, set implicit_transactions on  go   insert insert insert is internally turned into BEGIN TRAN Insert insert insert ... The above transaction will not be rolled back or committed unless the user issues the correct statement.

Without implicit transaction, which by default is the behavior of ADO (Auto Commit mode), the following is (conceptually) occurring: begin tran insert commit tran begin tran insert commit tran ... The following code sequence, written in Visual Basic, shows a difference between the raw SQL "BEGIN TRANSACTION" and the "set implicit_transactions on" issued when the ADO connection method BeginTrans is invoked:   Create a new standard .exe Visual Basic project. Add two command buttons to Form1, the default form. Command1.Caption : Use ADO Transactions Command2.Caption : Use T-SQL Transactions   Go to the references and add the following reference: Microsoft ActiveX Data Objects 2.0 Library  The code below uses the stores table of the Pubs database. Make sure you set the database properly in your connectstring.  Paste the following lines into the Code window for Form1:

Note You must change the User ID value and the password = value to the correct values 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 Command1_Click Cn.Execute "Delete from stores where stor_id LIKE '1%'" Cn.BeginTrans Cn.Execute "set implicit_transactions off" Cn.Execute "Insert INTO Stores(stor_id, _       stor_name,stor_address,city)" & _ "VALUES(101,'Store One','123 Oak St.','Seattle')" Cn.Execute "Insert INTO Stores(stor_id, _       stor_name,stor_address,city)" & _ "VALUES(102,'Store Two','123 Main St.','Tacoma')" Cn.RollbackTrans With rst .ActiveConnection = Cn         .CursorType = adOpenStatic .Source = "select * from stores where stor_id LIKE '10%'" .Open End With MsgBox rst.RecordCount rst.Close End Sub

Private Sub Command2_Click Cn.Execute "Delete from stores where stor_id LIKE '1%'" Cn.Execute "BEGIN TRANSACTION" Cn.Execute "set implicit_transactions off" Cn.Execute "Insert INTO Stores (stor_id, _      stor_name,stor_address,city)" & _ "VALUES(101,'Store One','123 Oak St.','Seattle')" Cn.Execute "Insert INTO Stores (stor_id, _      stor_name,stor_address,city)" & _ "VALUES(102,'Store Two','123 Main St.','Tacoma')" Cn.Execute "ROLLBACK TRANSACTION" With rst .ActiveConnection = Cn         .CursorType = adOpenStatic .Source = "select * from stores where stor_id LIKE '10%'" .Open End With MsgBox rst.RecordCount rst.Close End Sub

Private Sub Form_Load Dim strConn As String strConn = "Provider=SQLOLEDB;User ID= ;Password= ;Data" & _ "Source=(local);database=pubs" Cn.Open strConn Cn.CursorLocation = adUseClient Command1.Caption = "Use ADO Transactions" Command2.Caption = "Use T-SQL Transactions" End Sub  Click on Command1. You will get a RecordCount of 2. Click on Command2. You will get a RecordCount of 0.</ol>

Reason for the Difference in Results
With ADO Connection.BeginTrans will not increment @@TranCount until any of the following statements are encountered: <pre class="fixed_text">  FETCH      ALTER TABLE DELETE    INSERT CREATE    OPEN GRANT     REVOKE DROP      TRUNCATE TABLE SELECT    UPDATE When this option (set implicit_transactions on) is turned on and if there are no outstanding transactions, every ANSI SQL statement will automatically start a transaction. If there is an open transaction, no new transaction will be started. This transaction has to be explicitly committed by the user by using the command COMMIT TRANSACTION for the changes to take affect and the locks to be released.

Whereas executing Cn.Execute "BEGIN TRANSACTION" starts an explicit transaction and @@TranCount is immediately incremented by 1.

In the above Visual Basic code for the procedure Command1_Click the @@TranCount remains at 0 and the Rollback has no effect because before executing the first insert, you revert back to Auto Commit mode by issuing Cn.Execute "set implicit_transactions off."

Whereas in the Visual Basic code for procedure Command2_Click, the @@TranCount is immediately incremented to 1 when you issue Cn.Execute "BEGIN TRANSACTION; hence, the subsequent RollBack works.

The SQL Server 6.5 Trace utility is very useful for observing the Transaction commands this sample issues.

<div class="references_section">