Help and Support
 

powered byLive Search

INFO: Understanding ADO Transactions with MS SQL Server

Retired KB ArticleThis article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
View products that this article applies to.
Article ID:198024
Last Review:October 31, 2003
Revision:2.0
This article was previously published under Q198024
On This Page

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.

Back to the top

MORE INFORMATION

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:
1.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
2.Go to the references and add the following reference: 
      Microsoft ActiveX Data Objects 2.0 Library
3.The code below uses the stores table of the Pubs database. Make sure you set the database properly in your connectstring.
4.Paste the following lines into the Code window for Form1:

Note You must change the User ID <username> value and the password =<strong 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=<username>;Password=<strong 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
5.Click on Command1. You will get a RecordCount of 2.
6.Click on Command2. You will get a RecordCount of 0.

Back to the top

Reason for the Difference in Results

With ADO Connection.BeginTrans will not increment @@TranCount until any of the following statements are encountered: 
   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.

Back to the top

REFERENCES

For additional information, please see the following article(s) in the Microsoft Knowledge Base:
177138 (http://support.microsoft.com/kb/177138/EN-US/) INFO: Nested Transactions Not Available in ODBC/OLE DB/ADO

Back to the top

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

Back to the top

Keywords: 
kbcode kbdatabase kbinfo KB198024

Back to the top

Article Translations

 

Other Support Options

  • Need More Help?
    Contact a Support professional by E-mail, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.