Author: Tomas Rutkauskas 

MS-SQL : connection is in use by another statement

Answer:

When porting a larger database application (130k LOC) that worked fine with Oracle 
and InterBase to MS-SQL (6.5), I frequently got the error message 'connection is in 
use by another statement'.

At first, creating a new TDatabase for each TTable/ TQuery seemed to be necessary.

Then I found what was 'wrong' (not really wrong.. :-)

To speed up some of my queries, I had set the property Unidirectional to true. 
Delphi creates for such queries only one cursor (versus two for bidirectional 
queries or TTables). After removing the assignments of Unidirectional := true the 
error message disappeared and everything worked fine.

The following code resulted in the exception 'connection is in use by another 
statement':
1   
2   // dataBaseNameS : string  is the name of the alias (MS-SQL 6.5)
3   begin
4     Query1 := TQuery.Create(Application);
5     with Query1 do
6     begin
7       DatabaseName := dataBaseNameS;
8       SQL.Text := 'SELECT * FROM ABLESTOP';
9       // the exception disappears if the following is removed
10      Unidirectional := True;
11      Open;
12    end;
13    ShowMessage('ok')
14  
15    Table1 := TTable.Create(Self);
16    with Table1 do
17    begin
18      DatabaseName := dataBaseNameS;
19      TableName := 'COMPONENT_PLAN';
20      UpdateMode := upWhereKeyOnly;
21      Open
22    end;
23  
24    Table1.Insert;
25    Table1.FieldByName('PARTNO').AsString := IntToStr(GetTickCount);
26    Table1.FieldByName('ID').AsString := 'WWxx';
27    Table1.FieldByName('VERSION').AsInteger := 1;
28    // the exception will occurr in the next statement:
29    //     "Connection is in use by another statement"
30    Table1.Post;
31  end;