Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Question Unanswered: Executing Stored Procedures Within MS Query

    Hi Everyone...


    I'm trying to retrieve data from Microsoft SQL Server To Microsoft Excel using MS Query (MSQRY32.EXE).

    I want my end user to be able to change parameters in the query.
    When I use parameters in the where clause (for example: WHERE XXX=?) I get the data succesfully, BUT when I try to use parameters in Stored Procedurs I always get the same message : "Parameters are not allowed in queries that can't be displayed graphically".

    I tried MS Query Help, and it says I supose to run the procedure with hard coded values instead of parameters, save it, and then change the parameters to "?" (See image for details), BUT I GET THE SAME MESSAGE...

    If anybody can help, i will appreciate it.

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    simply design your query to get its parameters' value from cells in the worksheet and let your end users change the value of those cells
    qha_vn

  3. #3
    Join Date
    Feb 2004
    Posts
    4

    Red face thanks, but that's not my problem

    i can't change the parameters in the query if the query is a stored procedure. when i use a simple query my end users can change the parameters, but in SP's it saves the query WITH the parameters...

  4. #4
    Join Date
    Feb 2004
    Posts
    126
    Maybe this will help, I'm using a table-valued function, but it's the same concept:

    Code:
    Sub InsertParameterizedData()
    
        With ActiveSheet.QueryTables.Add(Connection:=Array( _
            "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=DatabaseName;Data Source=ServerName;Use Proc" _
            , _
            "edure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with colu" _
            , "mn collation when possible=False"), Destination:=ActiveCell.Range("A2"))
            .CommandType = xlCmdSql
            .CommandText = Array( _
            "SELECT * FROM DatabaseName.dbo.fnConsolidatedInvoices(" & ActiveCell & "," & ActiveCell.Range("B1") & ")")
            .Name = "NameYourQueryTableHere"
            .Refresh BackgroundQuery:=False
        End With
    End Sub
    The macro above returns the data from fn.ConsolidateInvoices(@StartDate,@EndDate) where I have replaced the parameter values with the value in the ActiveCell and the cell to the right of it.

    It then places the data beginning on the cell one row below the ActiveCell.

    I got all this using the Macro Recorder (God's gift to those of us unfortunate enough to work with Excel on a regular basis).

    Let me know if you still need any help on this.

    You can actually replace the "SELECT *..." with any SQL statement you want, and concatenate the cell addresses of the parameters you want, it's pretty damned cool if you ask me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •