Solved

hstmt error using RDO to call SQL from VB

Posted on 1998-06-29
2
876 Views
Last Modified: 2008-02-01
When calling a SQl stored proc using RDO I get:

RunTime Error 40002
S1000: {Microsoft}{ODBC SQL Server Driver}  Connection is busy with results for another hstmt

I know what an hstmt is (roughly!) but not how to solve this.  I tried looping while rs.StillExecuting = True, and tested the other defined recordset in my routine from the command line, and neither where executing while the error came up.

Can you help???
0
Comment
Question by:gwilym
  • 2
2 Comments
 
LVL 2

Accepted Solution

by:
connex earned 100 total points
ID: 1091636
Ok found the following in the KB:

PRB: VB ODBC Error "Connection is busy with results..."
Last reviewed: March 29, 1996
Article ID: Q119023  
The information in this article applies to:
Professional and Enterprise Editions of Microsoft Visual Basic, 16-bit and 32-bit, for Windows, version 4.0
Professional Edition of Microsoft Visual Basic for Windows, version 3.0
Microsoft Jet 2.0/Visual Basic 3.0 Compatibility Layer


SUMMARY
When accessing an SQL Server database as an ODBC data source in Visual Basic versions 3.0 or 4.0 with the Microsoft Access version 2.0 Compatibility Layer installed, error message 3146 may occur:


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

This error is the result of the SQL Server ODBC driver. The driver can only handle one active statement at a time. The statement remains active until all the rows are fetched.


MORE INFORMATION
This problem occurs because of a change in the behavior of the Microsoft Access engine between versions 1.1 and 2.0. In Visual Basic version 3.0, as shipped with the database engine in Microsoft Access version 1.1, when the DB_SQLPASSTHROUGH flag was used with CreateSnapshot, CreateDynaset, or as a setting for the Options property of the data control, the engine fully populated all result sets before the next Visual Basic statement could be executed.

A fully populated result set means that all the rows or records in the result set have been visited and fetched to the client machine. This was accomplished by doing the equivalent of a <your data access object>.MoveLast implicitly before the method (CreateDynaset, CreateSnapshot, <your data control>.Refresh or the creation of the form with the data control) returned.

Using the Compatibility Layer and the database engine in Microsoft Access version 2.0, this automatic forcing of a fully populated result set no longer occurs. This design decision was made because fully populating result sets can be expensive in terms of performance. It was decided to allow the programmer the control over whether the result set is fully populated. This gives the programmer the flexibility of choosing whether to fully populate the result set and when, either by slow navigation or in an explicit <your data access object>.MoveLast at a point after the initial object creation.

This error occurs when there are pending results on a statement handle that is then used to execute another query. This causes a problem when the ODBC data source is a SQL Server (Microsoft or Sybase) because, owing to the architectural design, there can be only one active statement per connection on an SQL Server.

Therefore the SQL Server ODBC driver (SQLSRVR.DLL) cannot allow multiple active HSTMTs on a single connection handle or HDBC. An active statement is defined as a statement that has pending results; that is, the whole result set has not been read from the server.

When a result set is created with the DB_SQLPASSTHROUGH flag, an HSTMT, which is an ODBC statement handle, is tied up waiting on the pending fetch of the entire result set. If there are pending results on a connection when the next query is executed, the error occurs.

When the DB_SQLPASSTHROUGH flag is NOT used, the connection manager component of the engine creates additional connections for each of the statements, because these statements may be pending throughout the life of the data access object (DAO). In the case of dynasets or data controls (which are wrappers for a database object and a dynaset), because these represent updateable result sets, a two-way connection must be maintained.

NOTE: These connections (HDBCs) can be recycled by the connection manager as they go idle. The time they are maintained is a function of activity and the ConnectionTimeout setting in the [ODBC] section of the VB.INI or <your exename>.INI file. See the following articles in the Microsoft Knowledge Base for more information:


   ARTICLE-ID: Q110227
   TITLE     : PRB: ODBC Database Remains Open After a Close Method Used

   ARTICLE-ID: Q115237
   TITLE     : How to Use Temporary Tables in SQL Server from Visual Basic
               3.0

WORKAROUND
The solution for the changed behavior of the Microsoft Access 2.0 engine is to add an explicit <your data access object>.MoveLast after each query executed using DB_SQLPASSTHROUGH thereby forcing full population of the result set. This will be no slower than the original Visual Basic version 3.0 performance and possibly faster, and it will prevent the error from occurring.

For Microsoft Access version 1.1 compatibility, placing an extra (redundant) <your data access object>.MoveLast after the creation of the DAO will not cost any more, because the result set is already fully populated automatically.



CODE EXAMPLE
Use the following code to demonstrate and work around the problem (NOTE: all statements must be complete on one line):


   Dim db As database, sn As snapshot, sn2 As snapshot
   Dim sql As String

   Set db = OpenDatabase("", 0, 0,
    "odbc;dsn=texas;uid=sa;pwd=;database=library;")

   sql = "select * from adultwide where member_no < 2001"
   Set sn = db.CreateSnapshot(sql, 64)

   ' Uncomment the next line to work around the problem.
   ' sn.MoveLast

   sql = "select * from adultwide where member_no < 2001"
   Set sn2 = db.CreateSnapshot(sql)
   ' This second CreateSnapshot causes the error:
   '  ODBC--call failed. [Microsoft][ODBC SQL Server Driver]Connection
   '  is busy with results for another hstmt (#0).

Use the following code to trap the error:

   Dim db As database, sn As snapshot, sn2 As snapshot
   Dim sql As String

   On Error GoTo handle

   Set db = OpenDatabase("", 0,
   0,"odbc;dsn=texas;uid=sa;pwd=;database=library;"
   )

   sql = "select * from adultwide where member_no < 2001"
   Set sn = db.CreateSnapshot(sql, 64)

   sql = "select * from adultwide where member_no < 2001"
   Set sn2 = db.CreateSnapshot(sql)

   Exit Sub
   handle:
   ' This local error handler could call a central global handler
   ' and use a global object pointer (Global glbsn As snapshot) to
   ' track any pending snapshots; for example:
   '   Set sn = db.CreateSnapshot(sql, 64)
   '   Set glbsn=sn
   ' Then in the handler, do => glbsn.MoveLast

   sn.MoveLast
   Resume
 

0
 
LVL 2

Expert Comment

by:connex
ID: 1091637
oops forgot one .)

see Q143032 in the KB too. (http://support.microsoft.com/kb/)

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 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

21 Experts available now in Live!

Get 1:1 Help Now