Microsoft KB Archive/119023

= PRB: VB ODBC Error "Connection is busy with results..." =

Article ID: 119023

Article Last Modified on 11/7/2003

-

APPLIES TO


 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 16-bit Enterprise Edition
 * Microsoft Visual Basic 4.0 32-Bit Enterprise Edition
 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q119023



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 .MoveLast implicitly before the method (CreateDynaset, CreateSnapshot, .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 .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 .INI file. See the following articles in the Microsoft Knowledge Base for more information:

110227 : PRB: ODBC Database Remains Open After a Close Method Used

115237 : 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 .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) .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).

Note You must to change Username= and PWD = to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database. Dim db As database, sn As snapshot, sn2 As snapshot Dim sql As String

Set db = OpenDatabase("", 0, 0,   "odbc;dsn=texas;Username= ;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.

Note You must change Username= and PWD = to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database. 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;Username= ;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

Additional query words: 3.00 4.00 vb4all vb4win

Keywords: kbdatabase kbprb KB119023

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.