Multiple Recordsets? I don't think so...
by Ross Mack - GUI Computing
When calling SQL Server stored procedures from ADO 2.0 using the SQL Server OLEDB provider (PROVIDER=SQLOLEDB) you may get an error along these lines :
The problem here is that SQL Server has tried to return multiple recordsets - a function which is currently unsupported with the OLEDB provider. The ODBC OLEDB provider (PROVIDER=MSDASQL) does support it - but the SQL Server specific one does not. Go figure!
It can helpfully do this even when you least expect it, as it tries to return a rowcount for you - in, of course, a recordset.
So, what ADO does is not open a recordset at all. And end up issuing that wonderfully helpful error message.
You can usually circumvent this (rather obnoxious) behaviour by issuing a "Set nocount on" or by including such a statement in the stored proc itself. This prevents SQL Server trying to return rowcounts of any selects in the stored proc as separate recordsets.
This behaviour can be observed using a command object to execute the stored proc (set rs = cmd.Execute), executing via the Connection object (set rs = Conn.execute(sSQL)), or by using the recordset's open method (rs.open sSQL).
