Solved

Questions About Multiple Transactions  Handling on SQL Server 2000

Posted on 2006-11-28
3
412 Views
Last Modified: 2010-04-04
Dear Sir,
  I have another problem need your support!
  I am trying to start two transactions at a time on SQL Server 2000!
  I declaired aTD1, aTD2 as TTransactionDesc, and set TransactionID with 1 and 2. (Please reffer to the code posted below!)
  When starting transactions, error occurs: A transaction is already active!
  Is there anything I do wrong!

Thanks for your help again!

  The Params of my TSQLConnection:
    DriverName=MSSQL
    HostName=192.168.9.7
    DataBase=tdsys
    User_Name=db1
    Password=*****
    BlobSize=-1
    ErrorResourceFile=
    LocaleCode=0000
    MSSQL TransIsolation=ReadCommited
    OS Authentication=False
    Prepare SQL=False
    Multiple Transaction=True
 
The source code:

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DBXpress, StdCtrls, DB, SqlExpr;

type
  TForm1 = class(TForm)
    SQLConnection1: TSQLConnection;
    btnStart: TButton;
    btnEnd: TButton;
    lbStatus1: TLabel;
    lbStatus2: TLabel;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnEndClick(Sender: TObject);
    procedure btnStartClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
    aTD1, aTD2: TTransactionDesc;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.btnEndClick(Sender: TObject);
begin
  SQLConnection1.Rollback(aTD1);
  lbStatus1.Caption := 'End Transaction 1';
  SQLConnection1.Rollback(aTD2);
  lbStatus2.Caption := 'End Transaction 2';
end;

procedure TForm1.btnStartClick(Sender: TObject);
begin
  aTD1.TransactionID  := 1;
  aTD1.IsolationLevel := xilREADCOMMITTED;
  aTD2.TransactionID  := 2;
  aTD2.IsolationLevel := xilREADCOMMITTED;
  try
    SQLConnection1.StartTransaction(aTD1);
    lbStatus1.Caption := 'Start Transaction 1';
    SQLConnection1.StartTransaction(aTD2);
    lbStatus2.Caption := 'Start Transaction 2';
  except
    on E: Exception do ShowMessage(E.Message);
  end;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  SQLConnection1.Connected := False;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  SQLConnection1.Connected := True;
end;

end.
0
Comment
Question by:daniel710624
3 Comments
 
LVL 27

Accepted Solution

by:
kretzschmar earned 500 total points
ID: 18034804
well, not sure if i am correct (because of not having sql2000-server), but as far as i know is that you can with one connection only one transaction (or even nested transactions), but no independend transactions.

my advice would be to use different connections
(which may cause in two sessions, which are independend)

just a guess

meikl ;-)
0
 
LVL 21

Expert Comment

by:ziolko
ID: 18035538
agree with kretzschmar, use two different connections.

ziolko
0
 

Author Comment

by:daniel710624
ID: 18035814
So, 'Multiple Transaction=True'  in TSQLConnection.Params is useless?
I found that TSQLConnection.MultipleTransactionsSupported is False after TSQLConnection.Active set to True, does that means SQL Server doesn't support multi-transaction?
If that's......I will be damn..... :*o*:
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question