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

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.

This behavior is by design.

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.