Board index » delphi » Nested Transaction in ADO ?

Nested Transaction in ADO ?

Hello every body !

Is it possible to have nested Transaction in ADO Connections ?
I Tried that but recive this error "Only One Transaction may be ...."
(I use Delphi5 with ADO connection to SQL Server 7.0)

............................................................................
if it is not possible , why, in the following code, the First query dose not
work ?
but the second one runs peroperly. Means that [ExecSQL] runs with no error,
but no Employee is created, but [EmployeeID] of [Jobs] table sets to 999.

***********************************************************
MainConnection.BeginTrans ;

try
    with Temp_Qry do
    begin
        Active := FALSE;
        Sql.Clear;
        Sql.Add ('INSERT [Employee] (EmployeeID, Name)   VALUES
(999,'Jack')')
        ExecSQL;
    end;

    with Temp_Qry do
    begin
        Active := FALSE;
        Sql.Clear;
        Sql.Add ('update [Jobs] Set  EmployeeID= 999')
        ExecSQL;
    end;

    MainConnection.CommitTrans;

except
    MainConnection.RollbackTrans;
end;

 

Re:Nested Transaction in ADO ?


Quote
>Is it possible to have nested Transaction in ADO Connections ?
>I Tried that but recive this error "Only One Transaction may be ...."
>(I use Delphi5 with ADO connection to SQL Server 7.0)

No
http://support.microsoft.com/support/kb/articles/Q238/1/63.ASP

Quote
>............................................................................
>if it is not possible , why, in the following code, the First query dose not
>work ?
>but the second one runs peroperly. Means that [ExecSQL] runs with no error,
>but no Employee is created, but [EmployeeID] of [Jobs] table sets to 999.

There doesn't appear to be anything wrong with the query to me so if it doesn't
through and error my guess is it did work.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Nested Transaction in ADO ?


Try run SQL Server Profiler and watch what's going on.
Robi

Quote
"Mehrdad Khosravi" <m_khosr...@persian.com> wrote:
>Hello every body !

>Is it possible to have nested Transaction in ADO Connections ?
>I Tried that but recive this error "Only One Transaction may be ...."
>(I use Delphi5 with ADO connection to SQL Server 7.0)

>.............................................................................
>if it is not possible , why, in the following code, the First query dose not
>work ?
>but the second one runs peroperly. Means that [ExecSQL] runs with no error,
>but no Employee is created, but [EmployeeID] of [Jobs] table sets to 999.

>***********************************************************
>MainConnection.BeginTrans ;

>try
>    with Temp_Qry do
>    begin
>        Active := FALSE;
>        Sql.Clear;
>        Sql.Add ('INSERT [Employee] (EmployeeID, Name)   VALUES
>(999,'Jack')')
>        ExecSQL;
>    end;

>    with Temp_Qry do
>    begin
>        Active := FALSE;
>        Sql.Clear;
>        Sql.Add ('update [Jobs] Set  EmployeeID= 999')
>        ExecSQL;
>    end;

>    MainConnection.CommitTrans;

>except
>    MainConnection.RollbackTrans;
>end;

Other Threads