Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

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 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.

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.

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

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.