Executing Stored Procedures Within MS Query

shlomi_salmi · #1 (**permalink**) 02-18-04, 01:36

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

qha_vn · #2 (**permalink**) 02-19-04, 05:30

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

shlomi_salmi · #3 (**permalink**) 02-19-04, 10:37

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...

grrr223 · #4 (**permalink**) 03-10-04, 21:47

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech