Constructing SQL Statements for Cursors

The SQL Server Native Client 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 Native Client 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 Native Client 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.

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 and later, 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][SQL Server Native Client][SQL Server]
               Cursor type changed."

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

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

SqlState: 42000
pfNative: 16937
szErrorMsgString: [Microsoft][SQL Server Native Client][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][SQL Server Native Client][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][SQL Server Native Client][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.

See Also

Concepts

Executing Queries (ODBC)