DOCUMENT:Q182929  18-MAY-2001  [vbwin]
TITLE   :PRB: Stored Procedures with a Return Statement Can Cause Errors
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER::5.0
OPER/SYS:
KEYWORDS:kberrmsg kbVBp500 kbGrpDSVBDB

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - 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 has confirmed this to be 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)

Additional query words:

======================================================================
Keywords          : kberrmsg kbVBp500 kbGrpDSVBDB 
Technology        : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB500
Version           : :5.0
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.