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
LVL 16
Jerry PaladinoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
Hi Jerry,

In the command text field, write this and see if it works:

{Call usp_ManPowerPlanning}

We usually use "from Microsoft query" rather than SQL server so I'm not sure if it's exactly the same, but from the Microsoft Query option this is the way to call a stored procedure.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here's the answer from a similar question. in Excel 2013, with a couple of screen shots after that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Koen Van WielinkBusiness Intelligence SpecialistCommented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jerry PaladinoAuthor Commented:
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Jerry PaladinoAuthor Commented:
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
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.