Microsoft KB Archive/313861

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

Article ID: 313861

Article Last Modified on 5/8/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q313861



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.



STATUS
This behavior is by design.



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
  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  In Microsoft Visual Basic, create a new Standard EXE project. By default, Form1 is created. On the Project menu, click References, and then click to select the '''Microsoft ActiveX Data Objects 2. Library''' check box. Add a CommandButton control to Form1.  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 = &quot;Provider=SQLOLEDB.1;User ID=username;Password=password;&quot; & _ &quot;Initial Catalog=Northwind;Data Source=ServerName&quot; cn.ConnectionString = sConn cn.Open

With cmd Set .ActiveConnection = cn    .CommandText = &quot;myProc&quot; .CommandType = adCmdStoredProc .Prepared = True End With

Set rs = cmd.Execute rs.NextRecordset rs.Close cn.Close  Modify the ADO connection string in the sConn variable as appropriate for your environment.</li> Run the project. You receive different results and error messages depending on the following criteria:

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

</li> 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.

</li> 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.</li> 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.</li> 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

</li> 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

</li> 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.</li> 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.</li> 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.

</li> 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.

</li> 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.</li> 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.</li></ul> </li></ol>

Keywords: kbprb kbpending KB313861

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.