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

 

[xenou]

Well, the article from 4GuysAtRolla seems somewhat ambiguous on whether this can work in Access (hence, in Excel). Too bad. Seems like it would be neat to get it going.

 

[Norie]

Dan

How is this connected to using the raw SQL?

sqlText = "StoredProc '%UPC%'"

Isn't that just using the stored procedure?

Am I missing something there? Or am I just getting confused.

Oh, I actually found out how you can combine the results, change the stored procedures to functions that return tables.

For example I created this simple function, taking most of the details from an existing stored procedure.

CREATE FUNCTION [dbo].[fcn_SelectPeriod]
(
@Start datetime
)

RETURNS TABLE

AS

RETURN

SELECT * From tbllevel

WHERE Month(StartDate)>=month(@Start)

I then created this stored procedure.

CREATE PROCEDURE [dbo].[sp_multiplerecordsets]

AS

BEGIN

SELECT * FROM fcn_SelectPeriod('01 August 2010')


UNION


SELECT * FROM fcn_SelectPeriod ('01 September 2010')

END

No idea if that helps, but I thought I'd post it anyway.

 

[Blade Hunter]

Woohoo!!!!!!!!!!!!!!!! I got 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
    Cells.ClearContents
    Server = "SYDSBMESQL002"
    DatabaseUserName = "sa"
    DatabasePassword = "admin"
    Conn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=" & Server & ";USER ID=" & DatabaseUserName & ";PASSWORD=" & DatabasePassword
    Cmd.ActiveConnection = Conn
    Cmd.CommandType = adCmdText
    Cmd.CommandText = "BanditStaging.dbo.SP_PRODUCT_CHECK_UPC '%9332727018176%'"
    Set RS = Cmd.Execute
    For Y = 1 To 4
    X = 0
        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 + 2 + (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
        Set RS = RS.NextRecordset() '<-- I forgot the word "Set" at the start of here
    Next
    Application.Calculate
    Application.Calculation = xlCalculationAutomatic
End Sub

I can't believe I missed the word "Set"

 

[Blade Hunter]

Dan

How is this connected to using the raw SQL?

sqlText = "StoredProc '%UPC%'"

Isn't that just using the stored procedure?

It is, I was more pointing out that by using the Stored Procedure it is a lot less code in VBA than using the raw SQL in VBA.

Still, all good now, thanks for your help gents .

 

[xenou]

Good news. Yeah, seems obvious now ...