INF: Processing Procedures and Batches with Multiple SELECTS (156500)

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:

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:

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.