PRB: Opening a Dynaset on a SQL Server Stored Procedure

• The Microsoft Foundation Classes (MFC) included with: Microsoft Visual C++, 32-bit Edition, versions 2.0, 2.1, 2.2, 4.0, 4.1, 4.2, 5.0

SYMPTOMS

An attempt to call a SQL Server 6.x stored procedure from a dynaset-type CRecordset throws a CDBException if the procedure has any additional SELECT, INSERT, UPDATE or DELETE statement other than a single SELECT statement.

You will see the following error message on recordset Open (DB Tracing enabled):

   "Cannot open a cursor on a stored procedure that has anything other
   than a single select statement in it
   State:37000,Native:16937,Origin:[Microsoft][ODBC SQL Server Driver]
   [SQL Server]"

CAUSE

Calling the following stored procedure from a dynaset-type recordset will cause the error described above:

      CREATE PROCEDURE twosel AS
             BEGIN

              select * from myTable
              select * from myTable

             End

   What's New
    Server Cursors
      Using ODBC Cursors
        Creating Cursors

   "You will get a cursor on SQLExecDirect (Exec procedure_name or{Call
   procedure_name}) only if the procedure contains one SELECTstatement and
   nothing else. Otherwise, SQL Server generates an error message. Because
   of this restriction, you cannot use server cursors with the ODBC
   catalog functions (which use stored procedures that contain multiple
   SELECT statements)."

RESOLUTION

Use a snapshot (with the cursor library loaded) or readOnly forwardOnly- type recordset when the stored procedure has more data manipulation statements other than a single SELECT statement.

STATUS

This behavior is by design.

REFERENCES