Using Server Cursors with ADO
ADO and OLE DB map cursors over the result sets of executed SQL statements. SQLOLEDB implements these operations using server cursors, which are cursors implemented on the server and managed by API cursor functions.
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 remain at the default setting, adUseServer.
Server cursors are created only 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 cannot be used with statements that generate more than one recordset.
This restriction applies to all statements described in Generating Multiple Recordsets. For more information, see Generating Multiple Recordsets. If a server cursor is used with any statement that generates multiple recordsets, an application can return 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