excel odbc query problem--stored proc error

[jsteph]

Hi all,
I have a macro that calls a stored procedure which returns a set of data. This works fine until I put some code in the stored procedure prior to the SELECT statement. I have NoCount On, but it appears that the Excel QueryTables method returns the *first* select statment in the procedure and not the final one.

How can I make QueryTables return the final statement of the procedure?
Thanks,
--Jim

 

[SkipVought]

Hi,

What application?

What Stored Procedure data?

What SQL called from SP?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jsteph]

The app is Excel.
For simplicity (and I tested this to make sure) I've reduced the sp to this:

(proc name Test)

Set Nocount On
Select 'Hello'
Select 'Goodbye'

In Excel, I call

    With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=test;UID=test;pwd=test;APP=Excel;",Destination:=Range("j3"))

        .CommandText = Array("Exec test")
        .Name = "test"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

This returns "Hello", I need it to return "Goodbye"
If I remove the Select 'Hello', then this returns "Goodbye".

There are many reasons that the Select 'Hello' exists, it needs to be there (keeping in mind this is pseudocode).

In adodb recordsets, there is a NextRecordset property so that is essentially what I'm looking for here. I know of the CopyFromRecordset Excel methods but I want to see if this can be done within the confines of this QueryTables construct.
Thanks,
--Jim

 

[SkipVought]

This seems somewhat trivial, although I do not understand the reason for the Stored Procedure. I use MS Query frequently, to access data in remote database/tables.

What is THIS purpose?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jsteph]

The purpose is to return a set of order data on numerous sheets, which have this QueryTables method all over the place. The sql was previously hardcoded and the person who had created this originally had to change the code on a weekly basis based on a whole host of different reasons--not so much date issues but the fact that the source tables might change, the order of the columns might need to be different, new columns might need to be added, etc.

I showed the powers that be that with an sp nobody will ever have to edit raw sql in dozens of places in several sheets again--editing the final SELECT will do it wherever the sp is called, and the sp also recieves parameters from some cells in the sheet to allow dynamically adjusting the return set.

But the problem is that sp makes another call to gather other data and it's the other call that is what does this in.

I have solved this by making the other call separately but I wanted was to have it in one, clean package. I could also use the CopyFromRecordset method, and still may--it's just hat this QueryTables thing is already all over and this was a quick-and-dirty fix and I didn't want to spend too much time redoing everything.

--Jim

 

[PHV]

Have you tried to create a view based on the sp ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

I use MS Query and ADO in Excel, however, I have never used SPs. It seems to isolate me from what needs to happen. My code interacts with the stated needs, either to change the data source or to change the SQL. For example I have around 100 database functions that I publish to my user community for them to use on spreadsheets, like any other function, like this ADO function...

Function DateConversion([blue]sType[/blue] As String, [blue]sDate[/blue] As String) As Variant
'SkipVought 1/26/2005 
'--------------------------------------------------
' Access: A010PROD.FPRPTSAR.READ
'--------------------------------------------------
':converts  sType _
    CM  Calendar Date to Mfg Date _
    MC  Mfg Date to Calendar date
    
    Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    
    Set cnn = New ADODB.Connection
    
    sServer = "A010PROD"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="
    
    Set rst = New ADODB.Recordset
    
    Select Case UCase([blue]sType[/blue])
        Case "CM"
            sSQL = "Select M.MDAY "
            sSQL = sSQL & "FROM FPRPTSAR.T_CNTLDATE M "
            sSQL = sSQL & "Where M.CNTL_DATE='" & Format([blue]sDate[/blue], "d-mmm-yyyy") & "' "
        Case "MC"
            sSQL = "Select MIN(M.CNTL_DATE) "
            sSQL = sSQL & "FROM FPRPTSAR.T_CNTLDATE M "
            sSQL = sSQL & "Where M.MDAY=" & [blue]sDate[/blue] & " "
    End Select
    rst.Open sSQL, cnn, adOpenForwardOnly
    
    With rst
    On Error Resume Next
        If Not .BOF Or .EOF Then
            .MoveFirst
            DateConversion = .Fields(0).Value
        End If
    End With
    
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
End Function

In other cases I use MS Query or ADO to return a block of data to a sheet, again, manipulating the SQL or connection to suite the needs.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jsteph]

PHV,
Yes, the view only returns the first sql.

FWIW, I decided to take what was a 1 hour fix and make it a complete re-working.

I'm now using the NextRecordset method along with the CopyFromRecordset method and it works fine. This way I don't have to make two db calls, and I'm also able to fit in a lot more functionality to the whole thing than was originally feasible...it's just that it was time I didn't really have but there's always the weekend to catch up...
Thanks everyone,
--Jim