Re: Transactions.

From: Stephen Howe (stephenPOINThoweATtns-globalPOINTcom)
Date: 01/24/05 

Date: Mon, 24 Jan 2005 18:18:49 -0000

> Does anyone know if their is a way in ADO not ADO.NET to change the
> connection to use EXPLICIT TRANSACTIONS with SQL SERVER.

Just call methods BeginTrans, CommitTrans or RollbackTrans of a ADO
connection object.

This is from my SQL Server 2000 Books online on "Explicit Transactions".
You might find the extract useful

>>>>>>>>>>>>>
You can also use explicit transactions in OLE DB. Call the
ITransactionLocal::StartTransaction method to start a transaction. Call
either the ITransaction::Commit or ITransaction::Abort method with
fRetaining set to FALSE to end the transaction without automatically
starting another transaction.

In ADO, use the BeginTrans method on a Connection object to start an
explicit transaction. To end the transaction, call the Connection object's
CommitTrans or RollbackTrans methods.

The ODBC API does not support explicit transactions, only autocommit and
implicit transactions.

Explicit transaction mode lasts only for the duration of the transaction.
When the transaction ends, the connection returns to the transaction mode it
was in before the explicit transaction was started, either implicit or
autocommit mode.
>>>>>>>>>>>>>

You also might want to read up on ADO's IsolationLevel property of a
Connection object.

See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthbegintrans.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdproisolationlevel.asp

Stephen Howe

Relevant Pages

  • Re: Nested Transactions
    ... then it is possible that transaction was opened ... against one connection and rollback called against another one. ... Trying this with ADO and SQLOLEDB, ... > Rollback doesn't work, saying, there is no corresponding BEGIN statement. ...
    (microsoft.public.data.ado)
  • Re: ADO and ADO.net in the same transaction
    ... distributed transaction for it. ... thing - but with old ADO - you grab your data - keep the connection live, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Transactions across subroutines in ADO
    ... Though I'm using a module level variable for the connection. ... dim cnMain as New ADODB.connection ... But If I'm not in a transaction, ... Jet transactions can be nested (and is supported in ADO) ...
    (microsoft.public.access.formscoding)
  • Re: UpdateBatch Problem
    ... is there a specific reason for using explicit transactions? ... Have you tried letting ADO handle the transaction implicitly? ... Your RollBackTrans, rolled everything back on the server (internal cache ... You could either try not using explicit transaction or if letting ADO ...
    (borland.public.delphi.database.ado)
  • Re: Transactions and foreign keys
    ... SQL Server handles explicit transactions differently than Access/Jet. ... You need to perform the insert into the parent, ... What I am using is an OleDbConnection / Transaction, ...
    (microsoft.public.dotnet.framework.adonet)