Using Server Cursors with ADO

ADO and OLE DB map cursors over the result sets of executed SQL statements. SQLOLEDB implements these operations through the use of server cursors, which are cursors implemented on the server and managed by API cursor functions.

By default, an ADO application does not use Microsoft® SQL Server™ API server cursors with SQLOLEDB. The default cursor used by the ADO application is read-only and forward-only and uses default result set processing.

Cursor Options with SQL Server

Because ADO allows the setting of cursor properties, a number of options exist for using cursors with ADO and SQL Server:

• Leave all cursor properties set to their defaults.

  If you use these settings, the provider uses default result set processing (forward only and read-only cursor). The default settings allow a program to execute any Transact-SQL statement; however, only one statement can be active on any connection at a time. The program must either fetch all the rows or cancel the result set before another statement can be executed on the same connection. Following those rules, a program can process Transact-SQL statements or stored procedures that allow multiple result sets.

• Change the default cursor type or lock type.

  The provider then uses SQL Server API server cursors to deliver the requested cursor functionality. Although this option provides a wide range of cursor functionality, it introduces some restrictions. For example, you cannot execute any Transact-SQL statement, batch, or stored procedure that returns more than a single result set. However, it is possible to have multiple active statements on a single connection (meaning that there can be pending results in the statement handle), provided they are all executed with API server cursors.

• Use an ADO client cursor (set the CursorLocation property to adUseClient).

  In this case, ADO implements the cursor; therefore, the application can use only the capabilities supported by the ADO client cursors. The application cannot access the cursor capabilities of the underlying provider. Only a CursorType property of adOpenStatic (static cursor) is supported for a setting of adUseClient.

ADO Cursor Settings

An ADO application can control the cursor functionality through the following Recordset properties.

Server Cursor Details

To use a server cursor, an application can set these properties to anything other than the default value:

• Set the cursor type of the Recordset object to adOpenKeyset, adOpenDynamic, or adOpenStatic.
• Set the LockType of the Recordset object to adLockPessimistic, adLockOptimistic, or adLockBatchOptimistic.
• Set the CacheSize property to anything other than the default value of 1.

The CursorLocation property should be left at adUseServer, the default setting.

Server cursors are only created for statements that begin with:

• SELECT
• EXEC[ute] procedure_name
• call procedure_name

Even if an application explicitly requests a server cursor, server cursors are not created for statements such as INSERT.

Server cursors also cannot be used with statements that generate more than one recordset.

This restriction applies to all statements described in Generating Multiple Recordsets. If a server cursor is used with any statement that generates multiple recordsets, an application can get one of the following errors:

• “Cannot open a cursor on a stored procedure that has anything other than a single SELECT statement in it.”
• “sp_cursoropen. The statement parameter can only be a single SELECT statement or stored procedure.”

This example shows the opening of a dynamic server cursor:

Dim rs As New ADODB.Recordset

. . .

rs.Open "SELECT * FROM titles", , adOpenDynamic, adLockOptimistic

rs.Close

See Also