Why only some users receive the error message: "The connection is busy with the results for another command"

Question

I have a Delphi application that is connected to SQL Server DBB using the DevACt SDAC component, we have 200 software installations and only for the client, and some users 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 Filter Mode On

My application creates both db users and SQL account accounts:

• creating a new user, then there is no problem.
• changing user code in my application, this means that another db user and SQL account are being created, I have an error
• this problem occurs only with some users, and not with all

What I already tried without luck:

• remote and reinstalled database
• remote and reinstalled instance of SQL Server
• verified users / account properties in SQL Server (everything is fine)

If you need specific information, please tell me.

------------ NEW INFORMATION ------------

I checked better all the properties of the instance from Studio Management, and I noticed that the CPU has not been verified (see image below).

Instead of all the other typical SQL Server installations, I see the checkboxes filled. Could this be a problem?

Hope this helps you to help me ...

Answer 1

"The connection is busy with the results for another command," the error means that there are at least two queries that use the same connection. This problem may occur if you are using the same connection in multiple threads. To solve the problem in this case, you must have a connection (TMSConnection component) in each thread. Additionally, this problem may occur if the TCustomMSDataSet.FetchAll property is set to False. When FetchAll = False, executing such requests blocks the current session. To avoid blocking, OLEDB creates an additional session, which can lead to the error "Connection is busy with results for another command." To solve the problem in this case, you should set the TMSConnection.Options.MultipleActiveResultSets property to True. The MultipleActiveResultSets property allows you to support SQL Server Multiple Active Set (MARS) technology. It allows applications to have more than one pending connection request and, in particular, to have more than one active default result set for each connection. Note that the MultipleActiveResultSets property only works when using the native SQL client. Therefore, you should also set the TMSConnection.Options.Provider property for prNativeClient.

Answer 2

I just wanted to correct the answer of dataol and say that MARS_Connection should be set to “Yes” instead of “True” in order to include several 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 

Answer 3

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

Answer 4

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

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

therefore, in the case of Condition = 1, he gave two sets of results and called "The connection is busy with the results for another command"

Hope this helps you.

Edit: I realized that you are old enough, please share what you did to fix this error.

Answer 5

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