SET CURSORTYPE (E-SQL)

The SET CUSORTYPE statement sets the use of standard (DB-Library) or browse (one connection per cursor) cursors.

Syntax

SET CURSORTYPE {CUR_BROWSE | CUR_STANDARD}

Arguments

CUR_BROWSE

Specifies using browse cursors. Each browse cursor requires a separate connection to Microsoft® SQL Server™.

CUR_STANDARD (default)

Specifies using standard DB-Library cursors. A unique index must exist on the source table. Each standard cursor shares the same (single) connection to the SQL Server installation used by the application. This is recommended for cursors because it does not require a separate connection to SQL Server.

Remarks

After this statement is issued, it affects all subsequent cursor OPEN statements. Using the DECLARE CURSOR FOR UPDATE statement has the same effect as SET CURSORTYPE CUR_STANDARD.

To do positioned update or delete operations on a browse cursor, the SELECT statement used to open the browse cursor must include a FOR BROWSE clause, and the table must include a timestamp column.

The SELECT statement used to open a standard cursor cannot contain any of the following Transact-SQL clauses:

For more information about restrictions on standard cursors with a SCROLLOPTION of DYNAMIC, see SET SCROLLOPTION.

If a cursor definition violates any of the conditions noted earlier, a SQLCODE of
-19521 is generated.

SQL Server treats each browse cursor connection as a different user. Locks held by one browse cursor can block operations attempted by other browse cursors. Because standard cursors share the same connection to SQL Server used by the application, using standard cursors eliminates potential browse cursor locking problems.

Examples

EXEC SQL SET CURSORTYPE CUR_STANDARD;

See Also