PRB: Stored Procedures with a Return Statement Can Cause Errors (182929)


The information in this article applies to:

  • Microsoft Visual Basic Enterprise Edition for Windows 5.0
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

  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)
Modification Type:MinorLast Reviewed:4/18/2003
Keywords:kberrmsg kbprb KB182929
©2002 Microsoft Corporation. All rights reserved.