FireDAC / SQL Server "Connection is busy with results for another hstmt"

[Nathan Wild 3]

For reasons I can not explain, I am getting a [FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt exception when attempting to connect to one of the queries in my application.  Same code works properly connecting to the same database from another workstation, and the same code can connect to another database from workstation that exhibits the issue?!

 

The query is the detail in a master-detail relationship, if that matters?  I have this application running in half a dozen environments and it works everywhere but here?  Any ideas what to check for?  Any or all guidance appreciated!!

[Attila Kovacs 629]

Maybe accidentally set to async mode?

[Nathan Wild 3]

2 minutes ago, Attila Kovacs said:

Maybe accidentally set to async mode?

Possibly...  Where is this set?

[Attila Kovacs 629]

Only god and google knows.

[Nathan Wild 3]

ResourceOptions.CmdExecMode is set to amBlocking, not async... 

 

Any other ideas?

[Attila Kovacs 629]

"Fetchall" comes to mind. If a result set of a query is only partially fetched (because of the size) it blocks the connection.

 

http://docwiki.embarcadero.com/RADStudio/Rio/en/Fetching_Rows_(FireDAC)

Edited November 27, 2019 by Attila Kovacs

[Attila Kovacs 629]

IIRC this could also caused by blob fields in the query, which are only fetched if needed (by default).

[Nathan Wild 3]

Thanks...  I thought of the fetch mode also...  I have FetchOptions.AutoFetchAll set to afAll, and have tried FetchOptions.Mode as fmAll or fmOnDemand, with the same result!?

 

There are no BLOB fields in the dataset... 

[Attila Kovacs 629]

15 minutes ago, Nathan Wild said:

There are no BLOB fields in the dataset...

I assume you talking about every other active datasets (which could block) and not the one which is failing (which is blocked).

Well, backup and start to debug with simplifying the queries.

Also, compare the installed drivers on those workstations.

[braunbaer 2]

Maybe there is no Native Client installed?

[Lars Fosdal 1791]

This means you have two active queries on the same connection.

Do you use the same DB objects for multiple operations?

Do you properly clean up your dataset(s) between each operation?

Do you use the same connection shared between threads? (Don't do that - create one for each thread, remember to intialize COM in each thread)

Is MARS enabled?

Is SQLNCLI or MSSQLODBC 17 installed on the failing workstation? (https://www.microsoft.com/en-us/download/details.aspx?id=56567)

 

 

[Carl Efird 0]

I ran into this a while back.  I vaguely remember reading that it is due to a limitation of the SQL Server client which is installed as part of Office, something about multiple cursors.  I found 2 solutions
    1 - install and use the SQL Server Native Client
    2 - force retrieval of all records for each query from the server, i.e. use FDQuery's FetchAll method after opening/executing the query.
Since our system administrator does not want to install the Native Client on all machines, I opted to use the second solution. 

[Lars Fosdal 1791]

2 hours ago, Carl Efird said:

I ran into this a while back.  I vaguely remember reading that it is due to a limitation of the SQL Server client which is installed as part of Office, something about multiple cursors.  I found 2 solutions
    1 - install and use the SQL Server Native Client
    2 - force retrieval of all records for each query from the server, i.e. use FDQuery's FetchAll method after opening/executing the query.
Since our system administrator does not want to install the Native Client on all machines, I opted to use the second solution. 

Note that the SQLNCLI is deprecated and replaced by MS SQL ODBC driver 17.

[Lars Fosdal 1791]

If FetchAll is not used, and there is more than the default row limit number of rows, RecordCount may show the same as the row limit (f.x. 50).

 

This means that if you use a for loop

for var n:= 1 to RecSet.RecordCount

do begin

  //  ... process

  RecSet.MoveNext;

end;

You may only get the first 50 records, and not the actual number.

That could possibly lead to this situation where the RecSet is "unfinished".

 

It is safer to use

 

while not RecSet.EoF

do begin

  // ... process

  RecSet.MoveNext;

end;

 

which will fetch more chunks of 50 rows until all rows have been fetched - or use FetchAll 😛

 

 

[Nathan Wild 3]

8 hours ago, braunbaer said:

Maybe there is no Native Client installed?

I am connected to the master query in the master-detail relationship without issue.  This would not work without the client?

[Lars Fosdal 1791]

5 minutes ago, Nathan Wild said:

I am connected to the master query in the master-detail relationship without issue.  This would not work without the client?

MARS (Multiple Active Result Sets on the same connection) doesn't work without the advanced clients, IIRCC.

https://docs.microsoft.com/en-us/sql/relational-databases/native-client/features/using-multiple-active-result-sets-mars?view=sql-server-ver15

[Lars Fosdal 1791]

I'd start by identifying for certain which drivers that are being used by the two clients.

[Nathan Wild 3]

3 hours ago, Lars Fosdal said:

If FetchAll is not used, and there is more than the default row limit number of rows, RecordCount may show the same as the row limit (f.x. 50).

 

This means that if you use a for loop

for var n:= 1 to RecSet.RecordCount

do begin

  //  ... process

  RecSet.MoveNext;

end;

You may only get the first 50 records, and not the actual number.

That could possibly lead to this situation where the RecSet is "unfinished".

 

It is safer to use

 

while not RecSet.EoF

do begin

  // ... process

  RecSet.MoveNext;

end;

 

which will fetch more chunks of 50 rows until all rows have been fetched - or use FetchAll 😛

 

 

 

3 hours ago, Lars Fosdal said:

If FetchAll is not used, and there is more than the default row limit number of rows, RecordCount may show the same as the row limit (f.x. 50).

 

This means that if you use a for loop

for var n:= 1 to RecSet.RecordCount

do begin

  //  ... process

  RecSet.MoveNext;

end;

You may only get the first 50 records, and not the actual number.

That could possibly lead to this situation where the RecSet is "unfinished".

 

It is safer to use

 

while not RecSet.EoF

do begin

  // ... process

  RecSet.MoveNext;

end;

 

which will fetch more chunks of 50 rows until all rows have been fetched - or use FetchAll 😛

 

 

Mr. Fosdal...  If ever a random Canadian walks up and high-fives you at a conference of something, it is me...  I have no explanation of WHY, but even though my query was set to fmFetchAll, simply adding a call to the FetchAll() method fixed this issue.   Thanks so much, I would never have thought to try this...

 

[bazzer747 25]

The MS SQL ODBC driver 17 seems to requires other libraries to be installed.

[NamoRamana 3]

Was connecting to SQL server 14, creating FDConnection at run-time in a thread. Thread was running 3 queries on the same FDConnection. The program was fine in dev, but was getting this error in Prod. Following steps fixed it:

 

- Prod machine had only default sql server driver (10.xx, I guess) that came with a new PC. Downloaded the ODBC 17 driver (and redistribution files, if needed) mentioned elsewhere.

- Added following code:

 

    cnMain.DriverName := 'MSSQL';
    cnMain.Params.Add('Server=' + server);
    cnMain.Params.Add('Database=' + db);
    cnMain.Params.Add('User_name=' + username);
    cnMain.Params.Add('Password=' + pwd);
    cnMain.Params.Add('MARS=Yes');
    cnMain.Params.Add('OSAuthent=No');

 

- Even with the new driver(ODBC 17), if I remove MARS, I get the error. The FireDAC documentation says, MARS=Yes by default, but somehow it didn't work in my case.

 

Hope someone find it useful.

Thanks.

[Dmitry Arefiev 101]

This is because ODBC 17 is unknown version for FireDAC.