Image of Navigational Map linked to Home / Contents / Search Multiple Recordsets? I don't think so...

by Ross Mack - GUI Computing
Image of Line Break

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 application requested an operation which is invalid when this object is closed. Error code 3704."

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 ( sSQL).

Written by: Ross Mack
October '98

Image of Arrow linked to Previous Article Image of Arrow linked to Nexts Article
Image of Line Break