Firedac Query.open problem 'Connection is busy with results for another command'

[Bagata 0]

Hello,

I have one problem with Query.open on one of the forms in application.
This message is appearing only on this query, and more strange is that is appearing only with certain input parameters, with different data relates on different way.

Enabling MARS is not an option, with MARS enabled is working well, but is causing problems on other forms, so I need to put it in work without MARS enabled, 

I tried FDConnection.AbortJob, I even tried to close connection, and open again, but I am getting this message again and again. Any ideas how can be this fixed ?

   

[Anders Melander 1639]

Are you using asynchronous query execution (ResourceOptions.CmdExecMode <> amBlocking) anywhere?

[Bagata 0]

3 minutes ago, Anders Melander said:

Are you using asynchronous query execution (ResourceOptions.CmdExecMode <> amBlocking) anywhere?

nope 😕

[Anders Melander 1639]

Well, that sucks.

I think you can use management studio to determine if your connection has an active query. Maybe that can provide some clues.

[Bagata 0]

53 minutes ago, Anders Melander said:

Well, that sucks.

I think you can use management studio to determine if your connection has an active query. Maybe that can provide some clues.

I tried to catch at the moment before query.open happens, but there is nothing active for my spid

[Dmitry Arefiev 99]

There are two basic options:

1. Set FetchOptions.Mode=fmAll
2. Use MARS

[Anders Melander 1639]

On 12/17/2022 at 1:01 AM, Dmitry Arefiev said:

Set FetchOptions.Mode=fmAll

I would agree but he said that he:

On 12/16/2022 at 5:37 PM, Bagata said:

even tried to close connection, and open again

 

Doesn't closing the connection also close all open cursors associated with the connection?

[Brian Evans 83]

Note that any query / Transact-SQL Code that tries to return two or more results sets will produce that error when MARS is off. Look for an IF or other structures in the SQL code that could end up running more than one SELECT statement for some code paths. Could be inside a stored procedure or in code you send to execute. Another place it can happens is not fully reading one result before going on to another. 

[Dmitry Arefiev 99]

1 hour ago, Anders Melander said:

I would agree but he said that he:

 

Doesn't closing the connection also close all open cursors associated with the connection?

Yes, it is. But we are talking about "poesie", not about the "code" ...

[Anders Melander 1639]

Poesie? Well, that explains a lot.

[Dmitry Arefiev 99]

Sorry, poetry.

[Anders Melander 1639]

51 minutes ago, Dmitry Arefiev said:

Sorry, poetry.

Yes, I guessed that much 🙂 It's almost the same in Danish: Poesi