Microsoft KB Archive/156500

= INF: Processing Procedures and Batches with Multiple SELECTS =

Article ID: 156500

Article Last Modified on 11/21/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft Open Database Connectivity 2.5

-



This article was previously published under Q156500



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: kbhowto kbusage KB156500

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.