CODEWITHSUNDEEP
sql-serverdelphiado
MartynA
MartynA

Reputation: 30715

TAdoDataSet, TAdoStoredProc multiple result sets

These components have the ability to retrieve multiple result sets (e.g. from a stored proc) in one go, and using D5 I can successfully use NextRecordSet to get at the second and subsequent ones from a Sql Server.

However, this only works if I specify the cursor location as clClient; if I use clServer, I get a "Does not return multiple result sets" error. Is this an inherent limitation (e.g imposed by the MDac layer on the client) or can multiple recordsets be successfully retrieved from a server-side cursor?

Upvotes: 4

Views: 835

Answers (1)

Guillem Vicens
Guillem Vicens

Reputation: 3996

It is an inherent limitation to server side cursors. As stated in following MSDN link:

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.

Upvotes: 3

Related Questions