Connection is busy with results for another command
francisco
I'm encountering the "Connection is busy with results for another
command" message frecuently when performing queries from my c++
application against sql server express, after performing a db
migration from access where I didn't have this problem.
I've read each and every post I found regarding this error, but I
still can't find an answer.
My connection string is "Provider=SQLNCLI;Server=.
\SQLExpress;Database=[dbname];Trusted_Connection=Yes;"
I've tried many possible combination of properties for this
connection, including MARS and DataTypeCompatibility but the same
thing happens.
I'm using only one connection, and queries are serialized and
protected with a mutex.
Any hints?
fd.
Erland Sommarskog
By default, SQL Native Client opens a second connection behind your back, if
the physical connection is busy. This is controlled by the session property
DBPROP_MULTIPLECONNECTIONS. Have you altered this one? Now, Books Online
says that that this does not happen if there is a transaction active on
the connection. (I assume this refers to a connection started through
any Transaction object in OLE DB. The provider cannot know if you have
started a transaction down in SQL Server.)
In any case, the idea of open a second physical connection has many problems
and often results in errors difficult to understand. So if you set
DBPROP_MULTIPLECONNECTIONS to false, I recommend that you keep it that
way.
The problem with a busy connection often comes from the failure to
consume result sets in entirety. Since you have migrated from Access,
the issue in your case it may be due to a kind of result sets that does
may not exist in Access: rowcounts from INSERT/UPDATE/DELETE operations.
By default, SQL Server returns the number of rows affected by these
statements. These rowcounts produces a kind of result sets that you need
to pick up.
Thankfully there is a simpler way: issue SET NOCOUNT ON when you connect,
and SQL Server will stop producing these rowcounts.
By the way, you only said C++, but what API do you use? ADO? OLE DB
Consumer Templates? The naked OLE DB API?
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
francisco
> By default, SQL Native Client opens a second connection behind your back, if
> the physical connection is busy. This is controlled by the session property
> DBPROP_MULTIPLECONNECTIONS. Have you altered this one? Now, Books Online
no I didn't.
> and often results in errors difficult to understand. So if you set
> DBPROP_MULTIPLECONNECTIONS to false, I recommend that you keep it that
> way.
where can I set it? I suppose it's a server setting...
>
> Thankfully there is a simpler way: issue SET NOCOUNT ON when you connect,
> and SQL Server will stop producing these rowcounts.
All my stored procedures have this option set, perhaps when quering a
view or table I get this strange recordset with the row count?
>
> By the way, you only said C++, but what API do you use? ADO? OLE DB
> Consumer Templates? The naked OLE DB API?
>
I'm using ADO.
thanks for your answer, let's see when I get back to the office :)
fd.
Erland Sommarskog
>> and often results in errors difficult to understand. So if you set
>> DBPROP_MULTIPLECONNECTIONS to false, I recommend that you keep it that
>> way.
>
> where can I set it? I suppose it's a server setting...
No, it's a session property in OLE DB. You can probably set it from ADO
through the properties collection, but I don't how.
In any case, since you say that you haven't changed it, you have it set
to true, and thus MULTICONNECTIONS is not related to your problem. It
seems that ADO does want to open a new connection, becuasse you are in a
transaction.
> All my stored procedures have this option set, perhaps when quering a
> view or table I get this strange recordset with the row count?
You don't get these row counts with SELECT statements.
I'm afraid that with the information I have, I cannot give further
suggestions. I think you will need to debug and narrow down in which
situations you get these errors.