Microsoft KB Archive/182929

= PRB: Stored Procedures with a Return Statement Can Cause Errors =

Article ID: 182929

Article Last Modified on 4/18/2003

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Enterprise Edition

-



This article was previously published under Q182929



SYMPTOMS
Calling more than one OpenResultSet statement synchronously on the same connection can generate the error:

Run-time error '40002':

S1000 [Microsoft][ODBC SQL Server Driver] Connection is busy with

results for another hstmt.



RESOLUTION
There are four possible workarounds for this issue:


 * use rduseClientBatch and check the ResultSet's RowCount property prior to executing the next OpenResultSet statement.
 * remove the RETURN(0) statement from the Stored Procedure.
 * set NOCOUNT to ON as the first line of the Stored Procedure.
 * trap the error(s) and drop and re-establish the connection prior to issuing any more OpenResultSet statements.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
If any additional OpenResultSet calls are made on the same Connection Object it results in the error:

Run-time error '40002':

S1000: [Microsoft][ODBC SQL Server Driver]Connection is busy with

results for another hstmt

The problem can also occur under ADO where the error is:

Run-time error: '-2147217900 (80040e14)'

[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot open a cursor on

a stored procedure that has anything other than a single select

statement in it.

Steps to Reproduce Behavior
  Run the following SQL Server script on SQL Server (make sure it is run from the Pubs database):

if exists (select * from sysobjects     where id = object_id('dbo.test') and sysstat & 0xf = 4) drop procedure dbo.test GO

CREATE PROCEDURE test AS        select * from authors return(0) GO                        Start the Enterprise edition Visual Basic 5.0. Create a Standard EXE Project. Form1 is created by default. Add a Project reference to "Microsoft Remote Data Objects 2.0."  Add the following code to the default form's Load Method:

Dim en As rdoEnvironment Dim cn As rdoConnection Dim rs1 As rdoResultset Dim rs2 As rdoResultset Dim sql As String

Set en = rdoEngine.rdoEnvironments(0) en.CursorDriver = rdUseOdbc

Set cn = en.OpenConnection( _     dsName:="", _      Prompt:=rdDriverNoPrompt, _      Connect:="DRIVER={SQL SERVER};" _      & "SERVER=;UID=;PWD= ;DATABASE=")

sql = "Exec test" Set rs1 = cn.OpenResultset(sql, rdOpenStatic, rdConcurRowVer) Set rs2 = cn.OpenResultset(sql, rdOpenStatic, rdConcurRowVer)

</li></ol>

Keywords: kberrmsg kbprb KB182929

-

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

© Microsoft Corporation. All rights reserved.