Devart Forums

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

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.

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.

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