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++ 4.0 Standard Edition
- Microsoft Visual C++ 4.1 Subscription
- Microsoft Visual C++ 4.2 Enterprise Edition
- Microsoft Visual C++ 4.2 Professional Edition
- Microsoft Visual C++ 5.0 Enterprise Edition
- Microsoft Visual C++ 5.0 Professional Edition
- Microsoft Visual C++ 6.0 Enterprise Edition
- Microsoft Visual C++ 6.0 Professional Edition
- Microsoft Visual C++ 6.0 Standard Edition
This article was previously published under Q152520
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
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:
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.
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
MFC Encyclopedia article: "Recordset: Declaring a Class for
a Predefined Query"
Additional query words: MfcDatabase kbVC401 kbdse
Keywords: kbtshoot kberrmsg kbdatabase kbprb kbsqlprog KB152520