Error: Connection is busy with results for another command

[Morris Neuman]

I am running a C++ VS20008 service application using SQLNCLI client. I have
added Mars=Yes in the connection string. The server is SQLExpress 2005.
This is currently running on a development machine with Windows XP Pro SP3.
The client has about 5 threads which issue quiries on an ongoing bases about
every 10 secs.
There is no other database activity from any other clients. After sitting
for about an hour the following error gets returned by the SQLNCLI:
"ComObject Error: Code = 80004005, Code meaning = Unspecified error, Source
= Microsoft SQL Native Client, Description = Connection is busy with results
for another command ."

Is this a SQLExpress issue only?
I thought Mars=Yes would resolve this. What is causing this and how can I
fix it?
Thanks
Morris

--
Thanks
Morris

[William Vaughn (MVP)]

From Chapter 9: "MARS: Just say no".
MARS is not a cure-all for overlapping processes. I would take a different
approach. In many cases (and I don't know what architecture you're using)
handling connections yourself is actually easier--esp. in Windows Forms
applications. You don't need a Connection pool. You can create a background
worker thread to fetch data (almost) asynchronously (consider that the query
runs async but the rowset population (the fetch) run sync. In any case, if
you want to reuse a connection you have to complete the fetch of all of the
pending resultsets (or cancel) before starting another operation. MARS is
not helping here. Connections are cheap with SQL Server--it can handle
hundreds to thousands of connections.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

"Morris Neuman" <Mor...@online.nospam> wrote in message
news:DCAF59D9-112B-4269...@microsoft.com...

[Uri Dimant]

Morris

> Is this a SQLExpress issue only?

Coul be... Have you tried running the app on Dev. Edition?

BTW I hope your SQL Server has at least SP3 installed on

"Morris Neuman" <Mor...@online.nospam> wrote in message
news:DCAF59D9-112B-4269...@microsoft.com...

[Charles Wang [MSFT]]

Hi Morris,
Did you use MARS with some T-SQL statements with the security context
switch EXECUTE AS/REVERT etc?
If so, this is the cause. Currently this is by design a limitation on MARS
though not documented officially.

To resolve this issue, please create a separate connection for executing
those statements with EXECUTE AS/REVERT.

Best regards,
Charles Wang

[Morris Neuman]

It is running on a Dev system with VS 2008 installed.
Will SP3 resolve it?
--
Thanks
Morris

"Uri Dimant" wrote:

> .
>

[Morris Neuman]

Charles
Thanks for your reply.

No, we don't issue any EXECUTE AS/REVERT T-SQL.
We're using C++ w/ADO to do all the Database work.

What else could cause that error msg? We never received that error when the
app uses ODBC to MS Access database as it has been running like that for many
years.

Now we migrated the app to SQL Express and experiencing these errors in a
very light activity load. I am concerned about SQL Express as an option to
replace the Jet engine now.

How many connections can I open against SQL Express?
If it is better to open a new connection for each thread in the app I'll do
that.
What do you sugggest?

--
Thanks
Morris

""Charles Wang [MSFT]"" wrote:

> .
>

[Erland Sommarskog]

Morris Neuman (Mor...@online.nospam) writes:
> What else could cause that error msg?

Without seeing your code, it's hard to tell. But let me say that I agree
entirely with Bill. MARS is not a feature I would recommend. It is difficult
to understand, and there are plenty of limitations.

> How many connections can I open against SQL Express?

Theoretically 32767.

> If it is better to open a new connection for each thread in the app I'll
> do that.

Absolutely! It was not clear from your original post whether you actually
had a single connection shared between threads, but if you have, that is
a design that is which seems wrong to me. Why do you have multiple threads?
To do things in parallel, I presume. But what happens if you have a single
connection?

1) You have a semaphore to control this common resource. Which of course
defeats the purpose of threads. (Unless the datbase connection is just
a very small part of the whole plot.)

2) You use MARS. Then you don't need the semaphore. But execution in
SQL Server on the different requests are interleaved, not parallel,
so you still get serialisation. You just show the semaphore down
to SQL Server.

The scenario where MARS makes (somewhat) sense is when you read rows
from a table, and you want update the rows as you pass along. Without
MARS you would have to read all rows first, which could be problematic
if there are very many rows.

But using MARS to share a single connection between threads? No way.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Morris Neuman]

Erland
I appreciate your feedback. You helped clear up some ideas.

The system is a multiline Telephony server so each line is a thread plus
there are about 5 background thread. There can be 120 line ie 120 threads +
5. All of them are accessing the database for reatrieval and update of
database record.

The basic design is over 10 years old when a database connection was an
expensive resource so it was shared amongst al the threads.
I'm more a telephony software expert than SQL expert so I wasn't sure that a
connection for each thread was a best practice for the situation.

In that vein is it just as well to create and destroy a connection for each
query? Or should each thread have a private connection for the duration of
execution?

Do you know if this issue is just for SQL EXPRESS or I will have the same
result with all SQL versions?

Thanks again for your response and the links you supplied.
--
Thanks
Morris

"Erland Sommarskog" wrote:

> .
>

[Erland Sommarskog]

Morris Neuman (Mor...@online.nospam) writes:
> The basic design is over 10 years old when a database connection was an
> expensive resource so it was shared amongst al the threads. I'm more a
> telephony software expert than SQL expert so I wasn't sure that a
> connection for each thread was a best practice for the situation.
>
> In that vein is it just as well to create and destroy a connection for
> each query? Or should each thread have a private connection for the
> duration of execution?

It's very common to write applications where you connect, run a query and
disconnect.

While this may seem expensive, it does not have to be, because of something
known as connection pooling. When you disconnect, the API lingers to the
connection for 60 seconds, and if you request for a new connection with the
same connection properties within those 60 seconds, a connection will be
reused from the pool if necessary.

If each thread keeps on connection during its entire lifetime, it can
indeed be a little expensive, and take up some memory both client-side
and server-side.

> Do you know if this issue is just for SQL EXPRESS or I will have the same
> result with all SQL versions?

I don't know exactly what you did, but from what you described, I see
nothing that would be specific to Express, so, yes, I think you would
see the same no matter which edition of SQL Server you would use.

> Thanks again for your response and the links you supplied.

The links are just part of my signature, and not related to this
question.

[William Vaughn (MVP)]

Erland is mostly right. However, the connection remains live in the pool for
4-8 minutes (a timeout that can't be altered in non-clustered systems).
SQL Express or any version of SQL Server does not implement the pool--it's
the client .NET (or OLE DB/ODBC) data provider that implements this
functionality.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9D1CEF46A...@127.0.0.1...

[Morris Neuman]

William
Thanks so much. I just posted a reply ro Erland that I switched provider
(hence client) and the errors stopped. Does that makes sense in light of
what you are saying?
--
Thanks
Morris

[William Vaughn (MVP)]

Yes. Each provider implements MARS and all other ADO.NET functionality
somewhat differently.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

"Morris Neuman" <Mor...@online.nospam> wrote in message
news:55E5F5CE-C49F-4AF1...@microsoft.com...