asked on

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

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.

JeroenW

ASKER

Edited text of question

alokm

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.

alokm

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.

JeroenW

ASKER

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

alokm

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.

Jim_SQL

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

Jim_SQL

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

ASKER CERTIFIED SOLUTION

Jim_SQL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JeroenW

ASKER

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.