INF: Processing Procedures and Batches with Multiple SELECTS

ID: Q156500

The information in this article applies to:

Microsoft SQL Server version 6.5
Microsoft Open Database Connectivity, 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 : WINDOWS:2.5; winnt:6.5
Platform : WINDOWS winnt
Issue type : kbhowto