Why only some users get the error: "Connection is busy with results for another command"

Question

I have a Delphi Application that is connected to a SQL Server db using SDAC component from DevArt, we have 200 installations of the software and only to a customer, with some users, I notice the following error:

"Connection is busy with results for another command" = "La connessione è occupata dai risultati di un altro comando".

SQL vers.: SQL Server 2008 R2 Express with filestream full enabled

My application create both db users and SQL account logins:

1. creating a new user, then there aren't problems
2. changing user code in my application, it means that another db user and SQL account login is created, I have the error
3. this problem happens only with some users, not all ones

What I've already tried without luck:

1. deleted and re-installed database
2. uninstalled and re-installed SQL Server Instance
3. checked users/account properties in SQL Server (all ok)

If you need specific infos please tell me

------------NEW INFORMATIONS------------

I checked better all the Instance properties from Studio Management and I've noticed that CPU's are not checked (see image below).

Instead in all the other normal installations of SQL Server, I see filled checkboxes. Could it be the problem?

I hope this help you to help me...

Anode

I haven't used sql server, however, I'm pretty sure there are settings which are limiting the number of connections or number of SQL's running at one time, I would investigate that before thinking that there's a problem with the components. – Duckbill

Ok, I checked also Instance properties and all seems ok: "Maximum number of concurrent connections = 0 = unlimited connections" – Anode

I tried to set process affinity using the following queries: ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO; ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE=0; but nothing happened. – Anode

if you're trying to execute a query from another thread, then you should create another instance of the database connection in that thread, make it connect to the server, connect query(ies) to it and enjoy (: – Duckbill

Answer 1

The "Connection is busy with results for another command" error means that there are at least two queries that use the same connection. This problem can occur if you are using one connection in several threads. To solve the problem in this case, you should have connection (the TMSConnection component) in each thread. Also, this problem can occur if you set the TCustomMSDataSet.FetchAll property to False. When FetchAll=False, execution of such queries blocks the current session. In order to avoid blocking OLEDB creates additional session that can cause the "Connection is busy with results for another command" error. To solve the problem in this case, you should set the TMSConnection.Options.MultipleActiveResultSets property to True. The MultipleActiveResultSets property enables support for the SQL Server Multiple Active Result Sets (MARS) technology. 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. Please note that the MultipleActiveResultSets property works only when SQL Native Client is used. Therefore, you should also set the TMSConnection.Options.Provider property to prNativeClient.

Thunderstorm

COMMENT MODIFICATION: But why i find this problem only on one particular customer? The SQL installation was the same as others. Those settings should influence all users that try to connect, not only someone of them right? – Anode

The Delphi connection string (e.g. TAdoConnection.ConnectionString property) the syntax is: "MARS Connection=True;" (w/o double quotes). – Amador

Answer 2

Just wanted to correct dataol's answer and say that MARS_Connection should be set to "Yes" instead of "True" to enable Multiple Active Result Sets. At least on SQL Server 2012 if you are using a DSN file:

[ODBC]
DRIVER=SQL Server Native Client 11.0
DATABASE=MYDBNAME
WSID=
Trusted_Connection=Yes
SERVER=
MARS_Connection=Yes

Verdha

This probably should be a comment to the solution (if you have the rights). I can add that in a Delphi connection string the syntax is: "MARS Connection=True;" (w/o double quotes). – Amador

Answer 3

To provide Multiple Active Result Set (MARS) support to a SQL connection using the MSSQL driver, you must add a key called Mars_Connection and set its value to True.

Absentee

Answer 4

@ienax_ridens, I recently encountered the same problem using the same tools (Delphi and Devart-SDAC). In my case one specific query giving two results sets. My TMSQuery was

If Condition= 1 
begin
 Select * from #TempTable1
end else 
begin
  -- Some more stuff
  Insert INTO #TempTable2
 --
 --
End
Select * from TempTable1 -- here is the problem

so in case of Condition = 1 it was giving two results sets and causing "Connection is busy with results for another command"

I hope this helps you.

Edit: I realized you post is quite old, please share what you did to resolve this error

Mariehamn

Looks like this is rather meant as a comment instead of an answer. – Stillas

Answer 5

I had the same problem and solved installing the microsoft odbc driver 11 (msodbcsql) (https://www.microsoft.com/pt-br/download/confirmation.aspx?id=36434).

Sleek

Answer 6

Check your compatibility mode i just ran into this when we moved from 2008 to 2016 db we had to set it to 2012 compatibility mode.

Crichton

Answer 7

I had this problem when I found that my runtime DLL was in the environment path and the program folder. It was a runtime issue and nothing with the program.

Run

Answer 8

"Connection is busy" can be reproduced like this:

conn = session.connection().connection
result = conn.cursor().execute("SELECT 1")  # avoid gc
conn.cursor().execute("SELECT 1")

The second cursor errors out as the first one is not consumed. This happens even if you use session.execute directly. There is a post by Micahel Bayer in this thread. See if you have any code patterns like this and avoid it. If you are using cursor directly, it can be closed using the closing context manager like this.

Germiston