Solved

ODBC: Connection is busy with results for another hstmt (#0)

Posted on 1998-03-02
9
1,965 Views
Last Modified: 2009-12-16
Hello,

I have a question that's dying for an answer.
I am a developer on a Access 2.0 & Sql Server 6.5 environment.

Lately, some of my users encountered a runtime error caused
by Microsoft Sql Server 6.5. The error doesn't occur consistently
or even frequently. Atleast untill yesterday. Since yesterday the
error occurs frequently, making the problem urgent.

The error:
ODBC-call failed. [Microsoft][ODBC SQL Server Driver] Connection is
busy with results for another hstmt [#0]

After some investigating on Microsoft Technet, I learned that this could
be caused by the way Sql Server handles PassThrough-queries. It can
only handle one PassThrough-statement at a time.

The funny thing is that the code, where-ever the errors occur, doesn't
contain PassThrough: Just plain Jet-engine selects and updates.

I also found that Virus-checkers may be the cause. Due to the checking
of all results, the time out may occur, in conjunction with the Times to try.

If you have any (good) ideas, please let me know. I am not expecting a
complete answer, just directions.

Jeroen.

0
Comment
Question by:JeroenW
  • 3
  • 3
  • 3
9 Comments
 
LVL 1

Author Comment

by:JeroenW
ID: 1090407
Edited text of question
0
 
LVL 2

Expert Comment

by:alokm
ID: 1090408
it seems to me that u are trying to do too many selects/updates on a single connection at a time.
i think u should open separate connection for updates.
0
 
LVL 2

Expert Comment

by:alokm
ID: 1090409
maybe the the first select's recordset object is not fully populated and then the update goes. That will give such errors. To remove this on the recordset do a movelast so that it gets fully populated and then give the other statement.
0
 
LVL 1

Author Comment

by:JeroenW
ID: 1090410
Dear Alokm,

Thanks for the comment. The sollution your giving is like the one Microsoft supplies:
Use a movelast to get the entire result from Sql Server.
This sollution works only for passThrough-queries (Microsoft says; please let me know if this MS comment is wrong, I have a lot of recordsets open at the time, but the problem didn't occur in the past while the code was pretty much the same).

I also have thought of your sollution, but reducing the open recordsets didn't supply the answer. Also, the errors didn't appear at the same place, which leads me to think it is something wrong in the settings.

Thanks,

Jeroen Westera

0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 2

Expert Comment

by:alokm
ID: 1090411
If it was working perfectly in the past , then i think the load on the server has increased because of maybe more users, more extensive requests and more locks on the tables.
0
 
LVL 2

Expert Comment

by:Jim_SQL
ID: 1090412
Hi,

Is this happening when using MS Access directly (no code), or is it happening when you pass SQL statements through VB (or another langauge) using ODBC/RDO/... code?

Jim

0
 
LVL 2

Expert Comment

by:Jim_SQL
ID: 1090413
Hi,

Is this happening when using MS Access directly (no code), or is it happening when you pass SQL statements through VB (or another langauge) using ODBC/RDO/... code?

Jim

0
 
LVL 2

Accepted Solution

by:
Jim_SQL earned 200 total points
ID: 1090414
Hi (again),

I'm going to assume it's through code (ODBC... or what ever). This would mean that the connections aren't being cleaned out. When you (or the programmers) get a result set they have to walk through every record AND RECORD SET. There may be 3 record sets each having multiple records and each one has to be accounted for (either by walking through each record and then through each record set, or by moving last in each record set, and then through each record set or by simply canceling the result sets.)
(Be carefull of aggregate functions.)

'Put the statement into the command buffer.
Result% = SqlCmd%(Sqlconn%, "SELECT data FROM table")
'Send the statement to SQL Server and start execution.
Result% = SqlExec%(Sqlconn%)
DO UNTIL Result% = NOMORERESULTS    'Process the statement results.
   Result% = SqlResults%(Sqlconn%)

   'Retrieve and process the data in each row.
   DO UNTIL SqlNextRow%(Sqlconn%) = NOMOREROWS
      'Code to print or process row of data.
   LOOP
LOOP

(This is sample code from books on line for "SqlResults%")

... I'd bet that someone has changed the query so that now it can return more than 1 result set....

P.S. My experience is mainly with VB through ODBC/RDO (i.e. my comments may not apply...?).

(Hope this helps.)
Jim

0
 
LVL 1

Author Comment

by:JeroenW
ID: 1090415
Hi Jim,

Thanks for your answer. The cancelling of the result-set (or rerunning all queries) works, but it is a lot of work as the errors occur in several parts of the application. It doesn't do good things for performance either.

Instead I've implemented a piece in each errorhandling routine:
if err = 3146 then 'the connection busy error...
  resume next
else
  ' the normal handling.
endif

I know it is not the most beautiful way, but at least the users can
continue making their bills, claims etc. I will stay on the lookout
for better sollutions.

Thanks again Jim

To alokm:
Thanks for your comments. There has been an increase in users
and the network-traffic increased even more. I am looking in to
reducing the traffic.

Greetings,

Jeroen.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now