Temporary Table cannot be found when I open other Query befo

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
mfg
Posts: 10
Joined: Wed 03 Oct 2007 13:49
Location: Zabrze

Temporary Table cannot be found when I open other Query befo

Post by mfg » Fri 27 Aug 2010 11:12

I would like to use a temporary table with TMSQuery but I have a strange problem - when I open other query where records count > FetchRows and FetchAll = false before doing something with query where I get information from temporary table I have an error message that temporary table doesn't exists.
Example (Q1 - ordinary query, QTmp - query to temporary table):

Q1.FetchAll := false;
Q1.FetchRows := 25;
Q1.SQL.Text := select top 26 * from table1;

QTmp.SQL.Text := select * from #tmp_table;

QTmp.Close;
QTmp.Open;

QTmp.Append;
QTmp.fieldByName('Field1').AsString := 'it works';
QTmp.Post; // the record is added to temporary table

Q1.Close;
Q1.Open;

QTmp.Append;
QTmp.fieldByName('Field1').AsString := 'failure';
QTmp.Post; // here I've an error message table doesn't exists

when I change
Q1.SQL.Text := select top 24 * from table1;
the number of records is lower than Q1.FetchRows
or I set Q1.FetchAll := true; everything works fine.

How to solve this problem ?
I use version 4.50.0.39.

kind regards
ZS

Dimon
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Mon 30 Aug 2010 09:10

To solve the problem set the TMSConnection.Options.MultipleActiveResultSets property to True. It allows applications to have more than one pending request per connection, and, in particular, to have more than one active default result set per connection.
Or you can fetch all records of the Q1 query (by calling the TDataset.Last method, for example) and after this work with a temporary table.

mfg
Posts: 10
Joined: Wed 03 Oct 2007 13:49
Location: Zabrze

Post by mfg » Mon 30 Aug 2010 21:51

I set TMSConnection.Options.MultipleActiveResultSets property to True but it doesn't help in my case. I still have got an error message 'Invalid object name #tmp_table'.
The second option (fetching all records) works, but I would like to avoid it, the number of records in Q1 is to high.

Dimon
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Tue 31 Aug 2010 07:44

This behaviour is connected with the specificity of work of SQL Server and we can't influence it.

Post Reply