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.