asked on

How to run a SQL Server Stored Procedure from an Excel Query

Trying to run a SQL Server Stored Procedure from Excel’s “Get External Data” facility. I can successfully read a Table from the database but my attempts to return the results from a store procedure have failed. A PDF file is attached that show the steps I have taken so far. The problem may be the syntax I am using to call the stored procedure from the connections properties dialog. I have tried several variations but no luck so far.

Any help or advice is appreciated.

Thanks,
Jerry
Run-Stored-Procedure---QueryTabl.pdf

SOLUTION

Koen Van Wielink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Koen Van Wielink

Jim's answer describes exactly what we do in our company as well. The only difference is the way calling the procedure (using Call vs. Exec). Both probably work. If you use parameters you can replace the hardcoded parameters in Jim's example with (?,?,?). One questionmark represents one parameter.
Follow his steps and you should be fine.

Jerry Paladino

ASKER

Thank you both for the quick response. I was hoping to use the OLDB connection but the ODBC connection from MS-Query is working so I will happily use this option for now.

Jerry

Jim Horn

Thanks for the split. Good luck with your project. -Jim

btw I originally created that so that my QA people can use this to test reports that have a bunch of parameters, and use the familar Excel filter by form functionality, instead of being forced to do all their testing on the report itself.

Perhaps I should collaborate with a couple of Excel experts and write an article on it.

Jerry Paladino

ASKER

Yes, MS-Query has a great built in Query Parameter facility. The Excel UI dialog box for parameters gives you several options and makes it easier for end users to manipulate the query. With the OLEDB connection the best solution is VBA for parameter manipulation.

You should write an article... There have been a lot of questions on using MS-Query and a solid article would be a great reference.

Thanks Again Jim!
Jerry

Koen Van Wielink

Let me know if you want help on that Jim. Unfortunately the majority of our ERP reports are using stored procedures called from Excel MS query, so plenty of experience here. As unmanageable as a BI solution based on Excel is, the flexibility for the end user is still second to none.