Many result sets TAdoDataSet, TAdoStoredProc

Question

These components have the ability to extract multiple result sets (for example, from a stored procedure) at a time, and with D5 I can successfully use NextRecordSet to get the second and subsequent from the Sql server.

However, this only works if I specify the location of the cursor as clClient; if I use clServer, I get the error "Do not return multiple result sets." Is this an inherent restriction (for example, an imposed MDac level on the client) or can multiple recordsets be successfully retrieved with a server-side cursor?

Answer 1

This is an inherent limitation for server cursors. As indicated in the following MSDN link :

Server cursors cannot be used with statements that generate more than one set of records.

This restriction applies to all claims described in Multiple Entries. For more information, see Creating Multiple Recordset. If the server cursor is used with any statement, generates several sets of records, the application may return one of the following errors:

• It is not possible to open the cursor on a stored procedure that has something other than one SELECT statement.
• sp_cursoropen. A statement parameter can be only one SELECT statement or a stored procedure.