Constructing SQL Statements for Cursors

The Microsoft® SQL Server™ ODBC driver uses server cursors to implement the cursor functionality defined in the ODBC specification. An ODBC application controls the cursor behavior by using SQLSetStmtAttr to set different statement attributes. These are the attributes and their defaults.

Attribute	Default
SQL_ATTR_CONCURRENCY	SQL_CONCUR_READ_ONLY
SQL_ATTR_CURSOR_TYPE	SQL_CURSOR_FORWARD_ONLY
SQL_ATTR_CURSOR_SCROLLABLE	SQL_NONSCROLLABLE
SQL_ATTR_CURSOR_SENSITIVITY	SQL_UNSPECIFIED
SQL_ATTR_ROW_ARRAY_SIZE	1

When these options are set to their defaults at the time an SQL statement is executed, the SQL Server ODBC driver does not use a server cursor to implement the result set; instead, it uses a default result set. If any of these options are changed from their defaults at the time an SQL statement is executed, the SQL Server ODBC driver attempts to use a server cursor to implement the result set.

Default result sets support all of the Transact-SQL statements. There are no restrictions on the types of SQL statements that can be executed when using a default result set.

Server cursors do not support all Transact-SQL statements. Server cursors do not support any SQL statement that generates multiple result sets.

The following types of statements are not supported by server cursors:

• Batches

  SQL statements built from two or more individual SQL SELECT statements, for example:

  SELECT * FROM authors; SELECT * FROM titles
  

• Stored procedures with multiple SELECT statements

  SQL statements that execute a stored procedure containing more than one SELECT statement. This includes SELECT statements that fill parameters or variables.

• Keywords

  SQL statements containing the keywords COMPUTE, COMPUTE BY, FOR BROWSE, or INTO.

In SQL Server, if an SQL statement that matches any of these conditions is executed with a server cursor, the server cursor is implicitly converted to a default result set. After SQLExecDirect or SQLExecute returns SQL_SUCCESS_WITH_INFO, the cursor attributes will be set back to their default settings.

In SQL Server version 6.5 or earlier, these statements cannot be executed with any of the statement attribute settings that would generate a server cursor. SQLExecDirect or SQLExecute return SQL_ERROR unless the cursor attributes are first set to their defaults to generate a default result set.

SQL statements that do not fit the categories above can be executed with any statement attribute settings; they work equally well with either a default result set or a server cursor.

Errors

In SQL Server 7.0, an attempt to execute a statement that produces multiple result sets generates SQL_SUCCESS_WITH INFO and the following message:

SqlState: 01S02"
pfNative: 0
szErrorMsgString: "[Microsoft][ODBC SQL Server Driver]
               Cursor type changed."

ODBC applications receiving this message can call SQLGetStmtAttr to determine the current cursor settings.

Attempting to execute statements that generate multiple results in SQL Server version 6.5 or earlier generates SQL_ERROR and one of the following messages depending on the type of statement executed.

An attempt to execute a procedure with multiple SELECT statements when using server cursors generates the following error:

SqlState: 42000
pfNative: 16937
szErrorMsgString: [Microsoft][ODBC SQL Server Driver][SQL Server]
               A server cursor is not allowed on a stored procedure
               with more than one SELECT statement in it. Use a
               default result set or client cursor.

An attempt to execute a batch with multiple SELECT statements when using server cursors generates the following error:

SqlState: 42000
pfNative: 16938
szErrorMsgString: [Microsoft][ODBC SQL Server Driver][SQL Server]
               sp_cursoropen. The statement parameter can only
               be a single SELECT statement or a single stored 
               procedure.

An attempt to execute a SELECT statement containing a COMPUTE clause when using server cursors generates the following error:

SqlState: 42000
pfNative: 16907
szErrorMsgString: [Microsoft][ODBC SQL Server Driver][SQL Server]
               'COMPUTE' is not allowed in cursor statements.

ODBC applications receiving these errors must reset all the cursor statement attributes to their defaults before attempting to execute the statement.

To set cursor options