Jump to content
Nathan Wild

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

Recommended Posts

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!!

Share this post


Link to post
2 minutes ago, Attila Kovacs said:

Maybe accidentally set to async mode?

Possibly...  Where is this set?

Share this post


Link to post

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... 

Share this post


Link to post
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.

Share this post


Link to post

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)

 

 

Share this post


Link to post

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. 

Share this post


Link to post
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.

Share this post


Link to post

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 😛

 

 

Share this post


Link to post
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?

Share this post


Link to post
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

Share this post


Link to post
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...

 

  • Like 1

Share this post


Link to post

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.

  • Like 1

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×