By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,738 Members | 1,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,738 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
(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
Jul 20 '05 #2

P: n/a
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
Jul 20 '05 #3

P: n/a
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
Jul 20 '05 #4

P: n/a
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
Jul 20 '05 #5

P: n/a
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
Jul 20 '05 #6

P: n/a
(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
Jul 20 '05 #7

P: n/a
(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
Jul 20 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.