ADO Error 3251: Current provider does not support returning multiple recordsets from a single execution?

[(Pete Cresswell)]

I posted this in the MS Access group, but no luck.
------------------------------------------
I've got another stored procedure in the same app that returns multiple
recordsets and the code works.

But now I've written another SP and the code traps out with the 3251 message.

The SP is writing two recordsets.

When I run the SP in Query Analyzer, both recordsets appear.

But when I step through the code, when the first RS should be there, it's
"Closed" and nothing I've tried will make it open.
Provider=SQLOLEDB.1 (which works on the other screen...)

Seems like I've been here before, but I can't remember what the problem was.
--
PeteCresswell

[Erland Sommarskog]

(Pete Cresswell) (x@y.z) writes:

I posted this in the MS Access group, but no luck.
------------------------------------------
I've got another stored procedure in the same app that returns multiple
recordsets and the code works.

But now I've written another SP and the code traps out with the 3251
message.

The SP is writing two recordsets.

When I run the SP in Query Analyzer, both recordsets appear.

But when I step through the code, when the first RS should be there, it's
"Closed" and nothing I've tried will make it open.

That is likely to be a rowcount from an INSERT into a temp table or
something like that. Step to next with .NextRecordset. Or use SET
NOCOUNT ON.

I don't think you can have multiple record sets with all settings.
But if you stick to client-side cursors, this should not be a problem.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[(Pete Cresswell)]

RE/

That is likely to be a rowcount from an INSERT into a temp table or
something like that. Step to next with .NextRecordset. Or use SET
NOCOUNT ON.

That was my first suspicion too...but it *looks* like I've got my NOCOUNT
settings in the right place.

If not, shouldn't the extraneous recordsets show up when I execute the procedure
under Query Analyzer?
--
PeteCresswell

[(Pete Cresswell)]

RE/

NOCOUNT

Then I jumped to the conclusion that maybe there was some kind of runtime error
in the query that I wasn't trapping....but I kept coming back to the fact that
everything looks a-ok when the query runs in Query Analyzer....and the
..CursorType is defaulted - the same as it is in a similar multi-recordset query
that works.
--
PeteCresswell

[(Pete Cresswell)]

RE/

same as it is in a similar multi-recordset query
that works.

One thing I notice in Query Analyzer is that when I toggle "Display Estimated
Execution Plan" on and off, the following error is thrown:
-------------------
Server: Msg 208, Level 16, State 1, Procedure spEstimate_Load, Line 139
Invalid object name '#Extract1'.
-------------------

But, as noted before, we see apparently-good output when the SP executes.

Also, the same error pops when the other procedure (the similar one that
works...) is up in Query Analyzer and "Display Estimated Execution Plan" is
toggled.
--
PeteCresswell

[(Pete Cresswell)]

RE/

no luck.

Bingo!

I was summing the contents of a #Temp table to coalesce some of the columns.

When I defined the table, I failed to specify DEFAULT 0 for the numeric fields.

Somehow, the Sum() statements worked when running under Query Analyzer, but
created 2 Closed/empty recordsets running from VBA/ADO.
--
PeteCresswell

[Erland Sommarskog]

(Pete Cresswell) (x@y.z) writes:

One thing I notice in Query Analyzer is that when I toggle "Display
Estimated Execution Plan" on and off, the following error is thrown:
-------------------
Server: Msg 208, Level 16, State 1, Procedure spEstimate_Load, Line 139
Invalid object name '#Extract1'.
-------------------

I would guess this is due to that when you use "Estimatade plan", QA runs
the query with SET FMTONLY ON. With this setting, SQL Server traverses
the batch, without executing the statements with one exception: it
enters stored procedures. You get this error, because it does not create
the temp table which appears further down in the procedure.

Now, there are situations where ADO can start to run queries with SET
FMTONLY ON. I've seen this happen if you call your stored procedure
with ODBC syntax and a .CommandType of adCmdText. Use adCmdStoredProcedure
instead.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

(Pete Cresswell) (x@y.z) writes:

I was summing the contents of a #Temp table to coalesce some of the
columns.

When I defined the table, I failed to specify DEFAULT 0 for the numeric
fields.

Somehow, the Sum() statements worked when running under Query Analyzer,
but created 2 Closed/empty recordsets running from VBA/ADO.

Hm, could that be because of the ANSI warnings for eliminating the
NULL values? Run the query from QA in text mode, and be sure to have
ANSI_WARNINGS ON, and you will see what I mean. (You will have to remove
the default of course.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp