Returning multiple resultsets from a SPROC into an ADODB recordset

[Isaac]

Is there any way to capture multiple outputs in cases where a t-sql stored procedure ends by doing 2-3 select statements - into an ADODB recordset?

I only am getting the first one which makes sense but is there any way to get all 4 of my outputs from the sproc?

 

[Minty]

You could use the SP to create four temporary SQL tables, get the data from them, then delete them?
Not very robust in a heavy multiuser environment unless the tables are uniquely named.

Are the outputs the same number of fields?

 

[Isaac]

The outputs are different # of columns

 

[WayneRyan]

Isaac,
Look at sp_help for a table.
It returns multiple recordsets.

ADODB has rs.MoveNextRecordset.

Hth,
Wayne

 

[Isaac]

Whoa .... ! MoveNextRecordset sounds like a possible major help, thank you! Guess I need to remember when all else fails, check out the methods list!

 

[Isaac]

I also found out something super amazingly weird.

So I created a function that executes a stored procedure to worksheets. The function accepts parameters like SPROC name, expected # of outputs, and an array for what to call the worksheet names.

I have a adodb.recordset variable of rs and another one of rsMaster (to hold the child and parent recordsets, respectively).

What really, really surprised me is that setting rs to rsMaster.NextRecordset method worked perfectly fine for the 2nd recordset - but not for the 3rd and beyond, I got an error about the current 'provider' does not support multiple recordsets in a single execution, or some such thing.

which is really weird because it has no trouble rsMaster.NextRecordset one time. (so moving to the 2nd output is fine - but moving to the 3rd is not).

after reading stack overflow posts, I've tried various connection string differences - provider sql oledb with integrated security sspi, with and without a driver spec as sqlserver, I've tried varying the cursorlocation to aduseserver, no difference.

so it seems I can make use of this for sproc's with 2 outputs, but not beyond that. my userbase is very consistent, at least I can know that if it consistently works for 2 then it will probably stasy that way.

thankfully I can still make good use of this tool I am creating, overall, for plenty of queries that only have 1 or 2 outputs.

thanks again all for looking & helping

 

[sonic8]

What really, really surprised me is that setting rs to rsMaster.NextRecordset method worked perfectly fine for the 2nd recordset - but not for the 3rd and beyond, I got an error about the current 'provider' does not support multiple recordsets in a single execution, or some such thing.

IIRC, the recordsets are organized more like a chain then a hierarchy.
If you used Set rs = rsMaster.NextRecordset to get the second recordset, you must use Set rsNo3 = rs.NextRecordset for the third, and so on for any subsequent recordsets.

 

[Isaac]

IIRC, the recordsets are organized more like a chain then a hierarchy.
If you used Set rs = rsMaster.NextRecordset to get the second recordset, you must use Set rsNo3 = rs.NextRecordset for the third, and so on for any subsequent recordsets.

somehow I never saw this last reply ... that's interesting. I may try it some day

 

[Albert D. Kallal]

somehow I never saw this last reply ... that's interesting. I may try it some day

Keep in mind that to get the next recordset, you MUST use the current recordset!!!
(this explains why you only get the 2nd one).

So, WRONG:

Set rs2 = rsMaster.NextRecordset
Set rs3 = rsMaster.NextRecordset   ' WRONG

CORRECT:

Set rs1 = rsMaster
Set rs2 = rs1.NextRecordset
Set rs3 = rs2.NextRecordset
Set rs4 = rs3.NextRecordset

Note how you MUST use the last/current recordset you grabbed - you can NOT use the original reocrdset!!!

Hence, you getting the next one, but not any additional ones......

You can't/don't' refer to the original reocrdset here but MUST refer to the "last" or "current" recordset to get the next one.......

And, as a FYI, there is no "collection" or some "index" way to get at each recordset - ONLY option is above approach...

Also, in MOST cases, you need to be using a newer ODBC driver -- the older legacy driver I don't think works
(assuming you using ADO --> ODBC translations here, which we often do....).

R
albert