INF: Processing Procedures and Batches with Multiple SELECTS

Last reviewed: April 9, 1997
Article ID: Q156500

The information in this article applies to:

Microsoft SQL Server, version 6.5
Microsoft Open Database Connectivity driver, version 2.5

SUMMARY

This article discusses how to process procedures or batches with multiple select statements using the Microsoft SQL Server ODBC driver.

MORE INFORMATION

The Microsoft SQL Server ODBC drivers that shipped with Microsoft SQL Server 4.21a or earlier only supported the following default settings for the ODBC SQLSetStmtOption() cursor parameters:

   SQL_CURSOR_TYPE = SQL_CURSOR_FORWARD_ONLY
   SQL_CONCURRENCY = SQL_CONCUR_READ_ONLY
   SQL_ROWSET_SIZE = 1

Starting with driver that shipped with SQL Server 6.0, the Microsoft SQL Server ODBC drivers added support for all of the ODBC cursor type options. The Microsoft ODBC drivers do this by implementing the cursors through the server cursors introduced in SQL Server 6.0. Any time an ODBC application changes any of the three parameters listed above from their default settings, the driver uses server cursors to support all calls to SQLPrepare(), SQLExecute(), or SQLExecDirect().

The server cursors in SQL Server 6.0 and later have a restriction in that they do not support multiple result sets. This means that an application cannot use server cursors to run a stored procedure containing multiple select statements, nor can it run a batch containing multiple commands. For example, the following statement would not be supported on a server cursor:

   SQLExecDirect(hstmt,
                 "select * from authors; select * from titles",
                 SQL_NTS);

ODBC applications using the Microsoft SQL Server ODBC driver that want to run multiple statement procedures or batches must first set the SQLSetStmtOption cursor parameters back to their default values (listed above) so that the driver will not attempt to open a server cursor. Applications running multiple statement procedures or batches while using server cursors may produce the following errors:

   SQLState: 37000
   pfNative: 16937
   szErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]
               Cannot open a cursor on a stored procedure that
               has anything other than a single select statement
               in it.

   -or-

   SQLState: 37000
   pfNative: 16938
   szErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]
               sp_cursoropen.  The statement parameter can only
               be a single select or a single stored procedure.

Starting with the driver that shipped with SQL Server 6.5, the Microsoft SQL Server ODBC drivers may sometimes detect this condition themselves. The driver can change the cursor settings back to their default values, and then process the command without using a server cursor. If the driver does this, it returns a SQL_SUCCESS_WITH_INFO on the SQLExecute() or SQLExecDirect() commands, along with the following message:

   SQLState: 01S02
   pfNative: 0
   szErrorMsg: [Microsoft][ODBC SQL Server Driver]
               Cursor type changed.

ODBC applications getting any of these errors when attempting to use server cursors with multiple statement batches or stored procedures should be changed to use the default values for the SQLSetStmtOptions listed above.

Additional query words: TDS
Keywords : kbusage SSrvGen
Version : 2.5 6.5
Platform : WINDOWS
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 9, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.