Connection is busy with results for another command

[Lee Schipper]

After migrating an existing app from VB6 to VB .Net, SQL operations on ADO
started giving this error periodically:
Connection is busy with results for another command

I am still using the ADODB library -- I did not migrate to ADO.Net

Looking at the SQL Server enterprise manager, I see that the .NET version
opens 4 database connections, while the VB6 version only opens one (both
programs were tested on the same computer with the same databases.)

Anyone have insights on why ADODB would launch multiple connections which
then collide with one another?

Thanks,
Lee

 

[William \(Bill\) Vaughn]

I suspect that your VB6 program used fewer connections because
a.. The queries ran fast enough in VB6 to release the connections to the pool before another was needed. Since ADOc running in ADO.NET can be slower (all interfaces have to be made via COM interop), they might not be getting done in time.
b.. The version of MDAC you're using is different and handles connections differently. Remember that ADOc automatically opens more connections in some cases when it finds the connection is busy--other versions do not--they throw the aforementioned exception.
hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Lee Schipper]

Bill, thanks for the insights. In my research I also came across at least
one article you wrote -- thanks for that as well.

After some more digging I found that the problem was mundane: The old VB6
code was setting a few recordsets to Nothing without first calling
rs.Close().

The VB6 program handled it OK, but each time a recordset was destroyed
without being closed in .NET, the next operation caused ADO to open a new
database connection, leaving the old connection open -- often for quite some
time.

The VB6/.Net difference may be in .Net garbage collection. While calling
rs.Close() is the fix, I found that the open connections could also be
closed by calling GC.Collect():

rs.Open( ... )
Do While Not rs.Eof ... Loop
rs = Nothing ' BAD: .Close() not called
GS.Collect ' But this hack resolves it

No, that's not my fix ... but it was educational




I suspect that your VB6 program used fewer connections because
The queries ran fast enough in VB6 to release the connections to the pool
before another was needed. Since ADOc running in ADO.NET can be slower (all
interfaces have to be made via COM interop), they might not be getting done
in time.
The version of MDAC you're using is different and handles connections
differently. Remember that ADOc automatically opens more connections in some
cases when it finds the connection is busy--other versions do not--they
throw the aforementioned exception.
hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________