multiple recordsets from a single execution

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

multiple recordsets from a single execution

Author

Topic

ibryan
Starting Member

3 Posts

Posted - 2004-09-05 : 10:32:36

Hello,
I'm trying to run a stored procedure from ASP but I'm receiving the following error:

ADODB.Recordset error '800a0cb3'

Current provider does not support returning multiple recordsets from a single execution.

My connection string looks like this:
"Provider=MSDASQL;Driver={SQL Server};Server=sql1.pwebtech.com;Database=database;UID=id;PWD=pwd;"

From what I can tell I should be ok right? I'd appreciate any help on this one.

Thanks
Ian

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-05 : 12:14:40

this means that your sproc is returning more that one resultset:

something like:
alter procedure YourSproc
as

select * from MaTable
select * from MaTable1

go
in .net this is returned to dataset as 2 tables.

in asp (not .net) i don't think this is possible. if it is i haven't seen it yet.

you need to alter your sproc to return only one resultset.

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-05 : 13:33:07

Actually there is recordset.movenext for this,
but maybe MSDASQL doesn't support this.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-09-06 : 04:17:48

MSDASQL is the ODBC driver for SQL Server and yes, it will not support multiple recordsets. This driver is slow and doesn't support certain features. Use the OLEDB driver if you can, but its not easy to just change your default driver, though. It can break a lot of code, as it changes some default behaviour, especially with recordsets.

Maybe this result is unintentional, are you really returning more than one recordset in your query? Try putting "SET NOCOUNT ON" at the top of your stored proc, its a good practice anyway to have this statement on all stored procs.

Btw rockmoose, you meant Recordset.NextRecordset, didn't you?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources