Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

PRB: Recordset Does Not Open with A Stored Procedure that Returns a High Severity Error


View products that this article applies to.

This article was previously published under Q313861

↑ Back to the top


Symptoms

When you try to open an ActiveX Data Objects (ADO) recordset that a stored procedure populates, the recordset does not open if the stored procedure returns a high-severity error. If you try to access the value, you receive one of the following error messages:

NOTE: The message that you receive depends on factors such as the Microsoft Data Access Components (MDAC) version and the cursor location.
Current provider does not support returning multiple recordsets from a single execution.
-or-
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
-or-
Application-defined or object-defined error.
-or-
Object or provider is not capable of performing requested operation.
-or-
An unknown error has occurred.

↑ Back to the top


Status

This behavior is by design.

↑ Back to the top


More information

If the stored procedure returns multiple recordsets, a problem arises because the Nextrecordset method does not work on a closed recordset. A successful query may be returned before the error occurs; however, after the error is returned, the Nextrecordset method does not work.

Steps to Reproduce the Behavior

  1. Run the following queries in Microsoft SQL Server Query Analyzer:
    Use Northwind
    GO
    
    CREATE Procedure myProc
    As
    Set NOCOUNT ON
    SELECT * FROM Shippers
    RAISERROR('Test Error Message', 15, 1)
    SELECT * FROM Categories
    Return
    GO
    					
  2. In Microsoft Visual Basic, create a new Standard EXE project. By default, Form1 is created.
  3. On the Project menu, click References, and then click to select the Microsoft ActiveX Data Objects 2.x Library check box.
  4. Add a CommandButton control to Form1.
  5. Paste the following code in the code window of Form1:
        Dim cn As New ADODB.Connection
        Dim cmd As New ADODB.Command
        Dim rs As ADODB.Recordset
        Dim sConn As String
    
        sConn = "Provider=SQLOLEDB.1;User ID=username;Password=password;" & _
                "Initial Catalog=Northwind;Data Source=ServerName"
        cn.ConnectionString = sConn
        cn.Open
    
       With cmd
         Set .ActiveConnection = cn
         .CommandText = "myProc"
         .CommandType = adCmdStoredProc
         .Prepared = True
       End With
    
       Set rs = cmd.Execute
       rs.NextRecordset
       rs.Close
       cn.Close
    					
  6. Modify the ADO connection string in the sConn variable as appropriate for your environment.
  7. Run the project. You receive different results and error messages depending on the following criteria:

    • In MDAC versions 2.6 or 2.7, the cursor is client-side, and the RAISERROR statement returns a severity of 11 or higher.

      Result: RAISERROR text appears in the ADO errors collection when the recordset is opened. If you try to display something from the recordset, you receive the following error message:
      Operation is not allowed when the object is closed.
      If you then call the Nextrecordset method, an error occurs in Visual Basic, and you receive the following error message:
      Current provider does not support returning multiple recordsets from a single execution.
    • In MDAC versions 2.6 or 2.7, the cursor is server-side, the RAISERROR statement returns a severity of 11 or higher, and the NOCOUNT setting does not have an effect.

      Result: If you open the recordset, you receive the following error message:
      Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
      If you try to display the recordset data, the same error occurs, and the following error message appears in the ADO errors collection:
      Deferred prepare could not be completed.
      If you call the Nextrecordset method, you receive the following error message:
      Current provider does not support returning multiple recordsets from a single execution.
    • In MDAC 2.6, the cursor is client-side or server-side, the RAISERROR statement returns a severity of 10 or lower, and SET NOCOUNT is ON.

      Result: No error appears in the ADO errors collection and Nextrecordset works.
    • In MDAC 2.6, the cursor is client-side or server-side, the RAISERROR statement returns a severity of 10 or lower, and SET NOCOUNT is OFF.

      Result: This result is similar to the result when SET NOCOUNT is ON, except that the rs.open method first returns an empty recordset because of the Records Affected message. View Nextrecordset to observe the first data that is returned. After this, no error occurs and Nextrecordset works.
    • In MDAC 2.5, the cursor is client-side, the RAISERROR statement returns a severity of 11 or higher, and the NOCOUNT setting does not have an effect.

      Result: RAISERROR text appears in the ADO errors collection when the recordset is opened. If you try to display something from the recordset, you receive the following run-time error:
      Operation is not allowed when the object is closed.
      If you call the Nextrecordset method, you receive the following Visual Basic error message:
      Application-defined or object-defined error
    • In MDAC 2.5, the cursor is server-side, the RAISERROR statement returns a severity of 11 or higher, and the NOCOUNT setting does not have an effect.

      Result: When you open the recordset, you receive the following error message:
      Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
      If you try to display recordset data, the same error occurs.

      If you call the Nextrecordset method, you receive the following error message:
      Application-defined or object-defined error
    • In MDAC 2.5, the cursor is client-side or server-side, the RAISERROR statement returns a severity of 10 or lower, and SET NOCOUNT is ON.

      Result: No error appears in the ADO errors collection and Nextrecordset works.
    • In MDAC 2.5, the cursor is client-side or server-side, the RAISERROR statement returns a severity of 10 or lower, and SET NOCOUNT is OFF.

      Result: The result is similar to the result when SET NOCOUNT is ON, except that the rs.open method first returns an empty recordset because of the Records Affected message. View Nextrecordset to observe the first data that is returned. After this, no errors occur and Nextrecordset works.
    • In MDAC 2.1, cursor is client-side, the RAISERROR statement returns a severity of 11 or higher, and the NOCOUNT setting does not have an effect.

      Result: RAISERROR text appears in the ADO errors collection when recordset is opened. If you try to display something from the recordset, you receive the following run-time error message:
      The operation requested by the application is not allowed if the object is closed.
      If you call the Nextrecordset method, you receive the following Visual Basic error message:
      The operation requested by the application is not supported by the provider.
    • In MDAC 2.1, the cursor is server-side, the RAISERROR statement returns a severity of 11 or higher, and the NOCOUNT setting does not have an effect.

      Result: When you open the recordset, you receive the following error message:
      Errors Occurred.
      This error message appears in the ADO errors collection. If you try to display recordset data, you receive the same error message.

      If you call the Nextrecordset method, you receive the following error message:
      The operation requested by the application is not supported by the provider.
    • In MDAC 2.1, the cursor is client-side or server-side, the RAISERROR statement returns a severity of 10 or lower, and SET NOCOUNT is ON.

      Result: No error appears in the ADO errors collection and Nextrecordset works.
    • In MDAC 2.1, the cursor is client-side or server-side, the RAISERROR statement returns a severity of 10 or lower, and SET NOCOUNT is OFF.

      Result: The result is similar to the result when SET NOCOUNT is ON, except the rs.open method first returns an empty recordset because of the Records Affected message. View Nextrecordset to observe the first data that is returned. After this, no errors occur and Nextrecordset works correctly.

↑ Back to the top


Keywords: KB313861, kbprb, kbpending

↑ Back to the top

Article Info
Article ID : 313861
Revision : 3
Created on : 5/8/2003
Published on : 5/8/2003
Exists online : False
Views : 370