PRB: Stored Procedures with a Return Statement Can Cause Errors

• Microsoft Visual Basic Enterprise Edition for Windows, version 5.0

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 is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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

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

1. 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
      




2. Start the Enterprise edition Visual Basic 5.0. Create a Standard EXE Project. Form1 is created by default.



3. Add a Project reference to "Microsoft Remote Data Objects 2.0."



4. 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=<server_name>;UID=<user_id>;PWD=<pwd>;DATABASE=<db_name>")
   
         sql = "Exec test"
         Set rs1 = cn.OpenResultset(sql, rdOpenStatic, rdConcurRowVer)
         Set rs2 = cn.OpenResultset(sql, rdOpenStatic, rdConcurRowVer)
    

Keywords : kberrmsg kbVBp500 kbGrpVBDB
Version : WINDOWS:5.0
Platform : WINDOWS
Issue type : kbprb