"Connection is busy with results for another hstmt"

[Bill Shaw]

My app was working fine; then someone made a change to the server....

Suddenly, I'm getting this message:

40002:
S1000: [Microsoft][ODBC SQL Server Driver]
Connection is busy with results for another hstmt.

The app is VB6 talking to SQL Server 6.5 (SP4) over RDO.
The app will run through about 50 various update, delete, and select
statements. Then hangs when it hits an RdoConn.Execute Sql that fires a
stored procedure ( giving the error msg above).

The thing that confuses me is that the app still works fine on my box, but
not on the customer's. I've tried removing and reinstalling SQL Server and
reinstalling MDAC 2.1. Still no help.

Any ideas out there?

[JRStern]

Just guessing. Is anything async? Is everything on the same
connection? Is your box also the server? Well, those aren't even
guesses, those are questions. And here's another: are they running
the same queries as you? And yet another: do they have the same
access privileges as you? And finally: do any SPs work from the
customer box? Maybe the message is bogus, it's really a privilege
problem.

Joshua Stern
JRS...@gte.net

On Sat, 15 May 1999 22:17:40 -0400, "Bill Shaw" <ws...@erols.com>
wrote:

[Jim Davis]

This error will happen with some versions of sqlsrv32.dll when an
rdoResultset is not explicitly closed, goes out of socpe, and you try to
open another resultset. Say you connection variable is CN, check
CN.rdoResultSets.Count. You will probably find that there is an open result
set when you expect none. Make sure you do an RS.Close & Set RS = Nothing
when you are done with the resultset.

We experienced this when we first installed SQL Server ODBC driver 3.x.
Unfortunately, RDOs behaviour varies in a few places between each version of
the driver. SQL statements that worked in one version break in another, and
vice versa. This has been a major pain. Version 3.5 is especially
problematic and I would recommend not using it. If any of your customers
have 3.5 ask them to upgrade.

Since the drive to ADO, MS doesn't seem to care about breaking existing RDO
apps.

Bill Shaw wrote in message <7hl9sh$k34$1...@autumn.news.rcn.net>...

[Raul Cosio]

We also experiencied this problem when we ran SP's returning more than one
resultset, for example

create procedure ... bla bla
as
select result1 = 'result1'
select result2 = 'result2'
select result3 = 'result3'
go

When you open a rdoResultset you get result1, but if you try to run another
query
SQL shows the error "Connection is busy with results for another hstmt.".
Because your Resultset is waiting for result2 and result3, to get them
call rdoResultset.MoreResults, this function reads the next result and
returns true
if there are more available.

[Theo Verweij]

I had the same problem on several machines (all NT).
On all machines, I have solved the problem by reducing the foreground
application boost to the middle position (Control panel - Sytem - Performance
tab).

I do not fully understand why this is working, but the problem didn't come back
anymore. If anyone can explain to me why this works, please let me know.

Theo Verweij.

[Tibor Karaszi]

I'm not sure about this, but it might be worth a try:

Perhaps ODBC believes that the "x Row(s) affected" as a resultset?
If you exec procs, try to add SET NOCOUNT ON at the beginning of the proc.

--
Tibor Karaszi
MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.

Raul Cosio wrote in message <#yPBucJo#GA....@cppssbbsa02.microsoft.com>...