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.

LVL 16
Jerry PaladinoAsked:
Who is Participating?
Jim HornConnect With a Mentor Microsoft 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.
Koen Van WielinkConnect With a Mentor Business 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.
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.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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.

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.
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!
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.