Multi result set procs in ADO, is it possible?

[Blade Hunter]

Hi Guys, so I have a proc that when run in SQL Server returns a 4 teir window for the results (4 result sets) and each one contains juicy info that I want except the first one, Running the proc in VBA using ADO unfortunately only seems to return the first result set and dismisses the rest, is there something simple I can do (maybe the result set is in a 2D array??) to get all the results back in Excel?

Cheers

Dan

 

[Blade Hunter]

Xenou, this is how I tried to do it:

Sub MultiResultProcTest()
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command
Dim Row As Long
Dim Findex As Long
Dim Data As Worksheet
Dim X As Long
Dim Server As String
On Error Resume Next
    Application.Calculation = xlCalculationManual
    Set Data = Sheets(ActiveSheet.Name)
    Data.Select
    Server = "REMOVED"
    DatabaseUserName = "REMOVED"
    DatabasePassword = "REMOVED"
    Conn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=" & Server & ";USER ID=" & DatabaseUserName & ";PASSWORD=" & DatabasePassword
    Cmd.ActiveConnection = Conn
    Cmd.CommandType = adCmdText
    Cmd.CommandText = "StoredProc '%9332727018176%'"
    Set RS = Cmd.Execute
    For Y = 1 To 4
        For X = 0 To RS.Fields.Count - 1
            Data.Cells(1 + (Y * 4), X + IIf(ActiveSheet.Name = "Main", 1, 2)) = RS.Fields(X).Name
        Next
        'If the result set is smaller than the sheet then populate in one go otherwise post line by line until the sheet is full
        If RS.RecordCount < Rows.Count Then
            Data.Cells(Row + 1 + (Y * 4), Findex + IIf(ActiveSheet.Name = "Main", 1, 2)).CopyFromRecordset RS
        Else
            Do While Not RS.EOF
                Row = Row + 1
                For Findex = 0 To RS.Fields.Count - 1
                    Data.Cells(Row + 1 + (Y * 4), Findex + IIf(ActiveSheet.Name = "Main", 1, 2)) = RS.Fields(Findex).Value
                Next Findex
                RS.MoveNext
            Loop
            Row = 0
        End If
        RS.NextRecordset 'This and the loop it is in are what I added to try and get it to work.
    Next
    Application.Calculate
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[Blade Hunter]

Dan

That just seems like 4 select queries as far as I can see.

Are the columns/fields being returned the same?

I know you say the no of columns is different but I mean generally.

You can use queries in a UNION query that don't have all the fields, you just add the missing fields as expressions.

Perhaps not a good example, and may not help you but it might be an idea.

I'm going to find out if there is another way to find the results and see what the story is when the multiple recordsets are returned.

Or if they even are returned - only the first recordset being returned is kind of familiar from other things.

Yeah they are quite different, result set 2 and 4 have the same columns but the rest are completely different.

I am trying to basically use what is there (ie the proc) I don't really want to have to get the developers to spend time on customizing things for my apps, it should be the other way around .

And yes, only the first recordset is returned using traditional ADO code.

 

[xenou]

Hmm. Okay. So we know it doesn't work. Sorry to be the master of the obvious here. No idea. The results window in management studio isn't really a result set. It just happens to be able to display four result sets in succession (I could be wrong about that but that's how it seems to me). If you have the sql for the four statements, how about running them as raw sql statements?

 

[Blade Hunter]

If you have the sql for the four statements, how about running them as raw sql statements?

I think that is what I will have to do, at least I have access to the proc so I can crack it open and just grab the SQL, I sure wish I could have used the 1 proc though.

In the past with another app I did (the one I am most proud of and will always remember it as "The app of my career") we were using an Oracle DB and we wrote a proc which did loads of things for maintenance, my app would then simply specify criteria and fire it off, nothing to do with multi result set but the result was such a clean code base and easy to add more things to. I did some seriously cool stuff with that app like self updating, user based options based on the logged in user, *Sigh* such fond memories before it was made redundant :P

Anyway, nostaligic moment aside, I was hoping to achieve the same level of cleanliness as opposed to multiple chunks of SQL code but I guess it's not gonna happen .

Thanks anyway guys, appreciate your help on it.

 

[xenou]

Comparing your code and this thread, one difference is the driver used. Your using an OLEDB driver and they are using a sql server driver (?) (They may be running their code right on the server, though)

 

[xenou]

The link above also notes some problems at the very end with disconnected recordsets and the .NextRecordset method (don't know if that applies to you - you don't seem to be disconnecting the recordset here but nesting the recordsets might be tricky).

They also say Access didn't work which is strange - but maybe they never tried Excel so either way we could be SOL. BTW, I like this site a lot for their ADO pages, although I suspect their target audience is ASP/ASP.Net developers rather than Access/Excel developers. They have a nice article on disconnected recordsets -- lets avoid disconnected multiple recordsets though, please - {grin}.

 

[Norie]

How would the raw SQL make a difference?

Surely you would still end up with the same 4 data sets.

I actually found out you can use UNION with stored procedures that return recordsets.

 

[Blade Hunter]

How would the raw SQL make a difference?

Surely you would still end up with the same 4 data sets.

I actually found out you can use UNION with stored procedures that return recordsets.

Because in my VBA code I would have 4 sets of SQL statements to execute as opposed to 1. I would still have 4 result sets but this:

sqlText = "StoredProc '%UPC%'"

is a lot cleaner than

'Call 1
sqlText = "Select * from Somewhere "
sqlText = sqlText & "Where something = something "
sqlText = sqlText & "And somethingelse = somethingelse "
sqlText = sqlText & "Join blah "
 
'Call 2
sqlText = "Select * from Somewhere "
sqlText = sqlText & "Where something = something "
sqlText = sqlText & "And somethingelse = somethingelse "
sqlText = sqlText & "Join blah "
 
'Call 3
sqlText = "Select * from Somewhere "
sqlText = sqlText & "Where something = something "
sqlText = sqlText & "And somethingelse = somethingelse "
sqlText = sqlText & "Join blah "
 
'Call 4
sqlText = "Select * from Somewhere "
sqlText = sqlText & "Where something = something "
sqlText = sqlText & "And somethingelse = somethingelse "
sqlText = sqlText & "Join blah "

This proc already exists for other purpose, I was hoping to be able to use it, it's no problem to use the raw SQL but I was hoping to use what is already there, if at any point the DB goes through some changes or anything like that, the Proc is updated, If I go and build a bunch of tools all with hardcoded SQL then I need to update them. From a maintenance perspective using the SP would be "more efficient"

I suppose I could consider housing the raw SQL in a table on the DB and each time I need to run it I can pull it in via ADO then run it, this way I maintain 1 set of SQL code instead of multiple intances of it.

Of course there are alternative methods in Excel for distributable code, I guess it all comes down to the right one for the job.

 

[xenou]

[deleted] - not making a contribution

 

[Blade Hunter]

we should all have SQL Servers to play with at work.

Be careful what you wish for, this is how I ended up in this pickle in the first place

Hehehe your ninja edit makes it look like I am misquoting you