Is this possible?

When I attempt the following:

ADOConnection1.BeginTrans;
// do some stuff
ADOConnection1.BeginTrans;

I get the following error message: "Cannot start more transactions on this
session."

I read that ADO supports nested/stacked transactions.  Is this a limitation
of SQL Server?

I'm using D6 SP1 on Win2K, connecting to SQL Server 2000 machine.

Thanks for your help.

Rob Clawson

Yes it's possible, but you need do the transaction control in the SQL
statement(s) (and naming each transaction is a good idea), not the native
Delphi control:

SET XACT_ABORT ON

BEGIN TRAN name1

(first statement here)

   BEGIN TRAN name2

   (second statement here)

   COMMIT TRAN name2

COMMIT TRAN name1

Bruce

  Hi Bruce

  This sounds a lot like the problem that I'm encountering at the moment...

  Here is the sample code (Modified according to a reply by Vassiliev V.V.
to my post titled "Transactions : Starting, Committing and/or Rolling Back",
posted 21/01/2002 13:56, in this group) :

<CODE SAMPLE>

  { Begin the transaction }
  MyADOConnection.BeginTrans;
  { Work with MyADOStoredProcedure }
  with MyADOStoredProcedure do
    try
      { Set the parameters for MyADOStoredProcedure }
      Parameters.ParamByName('SomeParameterName').Value := SomeValue;
      { ... }
      { Execute the stored procedure }
      ExecProc;
      { Commit the transaction }
      MyADOConnection.CommitTrans;
    except
      { Roll back the transaction }
      MyADOConnection.RollbackTrans;
    end;

</CODE SAMPLE>

  I need to find a way to make it work without resorting to modifying my SQL
code (If possible).

  Is there any way that this can be accomplished while making use of
MyADOConnection.BeginTrans, MyADOConnection.CommitTrans and
MyADOConnection.RollbackTrans..?

news:3c4e07ce$1_2@dnews...
: Yes it's possible, but you need do the transaction control in the SQL
: statement(s) (and naming each transaction is a good idea), not the native
: Delphi control:
:
: SET XACT_ABORT ON
:
: BEGIN TRAN name1
:
: (first statement here)
:
:    BEGIN TRAN name2
:
:    (second statement here)
:
:    COMMIT TRAN name2
:
: COMMIT TRAN name1
:
: Bruce
:
<SNIP>

This might be of help:
http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q306649 The problem
is that the OLE DB Provider does not support nested transactions. To get
nested transactions, you will need to move your transaction control to the
database server side - e.g. inside a stored procedure.

Interestingly, it looks like Delphi's ADOExpress components are able to
support nested transactions if the underlying OLE DB provider does (have a
look at FTransactionLevel in ADODB.pas) - unfortunately none of them do so
there is nothing that Delphi can do about that.

Regards,
Andy Mackie

  Bummer...  It would seem that I have no alternative then...  :o/

  Thanx for the information anyway...

news:3c4e8d45$1_2@dnews...
: This might be of help:
: http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q306649 The
problem
: is that the OLE DB Provider does not support nested transactions. To get
: nested transactions, you will need to move your transaction control to the
: database server side - e.g. inside a stored procedure.
:
: Interestingly, it looks like Delphi's ADOExpress components are able to
: support nested transactions if the underlying OLE DB provider does (have a
: look at FTransactionLevel in ADODB.pas) - unfortunately none of them do so
: there is nothing that Delphi can do about that.
:
: Regards,
: Andy Mackie
:

Thanks for the info everybody. Since the limitation is in the OLE DB
provider, I'll either code around it or resort to a stored procedure.

Thanks!

Rob Clawson