An attempt to call a SQL Server 6.x stored procedure from a dynaset-type CRecordset throws a CDBException in Visual C++ (152520)
The information in this article applies to:
- The Microsoft Foundation Classes (MFC), when used with:
- Microsoft Visual C++, 32-bit Editions 2.0
- Microsoft Visual C++, 32-bit Editions 2.1
- Microsoft Visual C++, 32-bit Editions 2.2
- Microsoft Visual C++, 32-bit Editions 4.0
- Microsoft Visual C++, 32-bit Editions 4.1
- Microsoft Visual C++, 32-bit Enterprise Edition 4.2
- Microsoft Visual C++, 32-bit Professional Edition 4.2
- Microsoft Visual C++, 32-bit Enterprise Edition 5.0
- Microsoft Visual C++, 32-bit Professional Edition 5.0
- Microsoft Visual C++, 32-bit Enterprise Edition 6.0
- Microsoft Visual C++, 32-bit Professional Edition 6.0
- Microsoft Visual C++, 32-bit Learning Edition 6.0
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"
| Modification Type: | Major | Last Reviewed: | 6/3/2005 |
|---|
| Keywords: | kbtshoot kberrmsg kbDatabase kbprb kbSQLProg KB152520 kbAudDeveloper |
|---|
|