Microsoft KB Archive/152520

= An attempt to call a SQL Server 6.x stored procedure from a dynaset-type CRecordset throws a CDBException in Visual C++ =

Article ID: 152520

Article Last Modified on 11/21/2006

-

APPLIES TO

 Microsoft Foundation Class Library 4.2, when used with:  Microsoft Visual C++ 2.0 Professional Edition

 Microsoft Visual C++ 2.1

 Microsoft Visual C++ 2.2

 Microsoft Visual C++ 4.0 Standard Edition</li></ul>

 Microsoft Visual C++ 4.1 Subscription</li></ul>

 Microsoft Visual C++ 4.2 Enterprise Edition</li></ul>

 Microsoft Visual C++ 4.2 Professional Edition</li></ul>

 Microsoft Visual C++ 5.0 Enterprise Edition</li></ul>

 Microsoft Visual C++ 5.0 Professional Edition</li></ul>

 Microsoft Visual C++ 6.0 Enterprise Edition</li></ul>

 Microsoft Visual C++ 6.0 Professional Edition</li></ul>

 Microsoft Visual C++ 6.0 Standard Edition</li></ul> </li></ul>

-

<div class="notice_section">

This article was previously published under Q152520

<div class="symptoms_section">

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):

<pre class="fixed_text">  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]

<div class="cause_section">

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 This is by design as documented in the Help file for SQL Server ODBC driver version 2.5. You can navigate the Help file in the following way to get to the description: <pre class="fixed_text">  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)."

You will also get the same error message when using dynaset if your stored procedure has a RETURN statement in addition to a SELECT statement.

<div class="resolution_section">

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.

<div class="status_section">

STATUS
This behavior is by design.

<div class="references_section">