We all know we can use MS Query to get data from a SQL server. Typically though, we pull from a Table or a View. Well in some organizations, the IT department wants all interaction with the server to be done through Stored Procedure. This adds a level of risk management and makes the DBAs feel better.
So today, I’ll show you how to easily make Excel run a Stored Procedure to get data.
Step 1: Data tab – > From Other Sources -> From SQL Server
Step 2: Enter Credentials. Your server name can be an IP address
Step 3: Choose any old table or view. Pick a small one because we’ll discard it later anyway.
Step 4: Excel will pop up the Import Data dialog box. Click Properties here (NOT THE OK BUTTON).
Step 5: Click on the Definition tab. There, change Command Type to SQL, and then enter your Stored Procedure name in the Command Text input.
Step 6: Excel complains about something….blah…blah…blah. Click Yes – (as in yes I know what I’m doing).
Step 7: Excel will activate the Import Data dialog box again. This time click OK to fire the Stored Procedure and return the results.
Step 8: Marvel at your results
Notes:
- Excel will fire the Stored Procedure each time you “Refresh”
-
If you have to pass a parameter, you can enter it in the command text like this:
- If you have to pass dynamic parameters you’ll have to turn to VBA. See this post.
- I assume you can do this with ORACLE databases too.
- I’ve yet to test whether this will fire a Stored Procedure that doesn’t return data. In other words, Stored Procedures that perform Insert, Update or Delete actions. I assume that if you can, there is the possibility of updating SQL from Excel through a simple connection. Pretty cool.
RELATED STUFF
- Outta My Way Formula Thingy!
- Zoom into Spreadsheet on Double-Click
- Find and Highlight Specific Values
- Limiting Movement within Your Worksheet
- Filter on Selection in Excel
- Protect Worksheet Structure with Array Formulas
- Passing Multiple Values to One Parameter
- Running an Access Parameter Query from Excel
- An Easier Way to Remove Formatting
- Avoiding Data Cut-Off in Excel
If you have to pass dynamic parameters you’ll have to turn to VBA. I’ll do a post on this later this week. Goody! Perfect timing, I have to do one of these at work.
Dick et al answered some questions for me on this at http://www.dailydoseofexcel.com/archives/2010/04/28/dynamic-columns-in-ms-query/ and http://www.dailydoseofexcel.com/archives/2010/04/28/dynamic-columns-in-ms-query-ii/ in case this helps in any way.
And I found a lot of info at http://www.sommarskog.se/dynamic_sql.html that I’m working my way through that looks very comprehensive. But I’m looking forward to just the facts, maam re VBA
Hello, Thanks for this blog, very helpful, I have a problem, for some reason when I enter a parameter to my sp, I get a “Referance not valid” message….Would you know why?
This is dangerous on a lot of levels (password storing in excel to a “live” database).
Other issue is that the database vEmployee could be enormous in terms of fields (columns) and records (rows). If that database has 35000 employees (terminated and active) and there are 125 fields. Hope you have a lot of processing time.
As for Oracle (ie PeopleSoft) there are 500 tables that have to be joined. Good luck with that….
Kevin:
Your points are valid. I would counter with these:
1. You can use a ‘Service’ account that only has access to run controlled Stored procedures
2. The Stored Procedures should be tested and designed to minimize performance issues. Because the user is going through a Stored Procedure, it’s actually less dangerous than if they built an MS Query on their own.
3. Again, because the user is going through a pre-designed Stored Procedure, they would not have to know every nuance of the database. So if there are 500 tables to join, it would essentially be transparent to the user , as he is running a single Stored Procedure.
Luis: Sounds like your parameter name is not matching the one built into the Stored Procedure .
What if I need to pass two parameters? I’m separating with a comma and its only reading the first param.
This is very helpful! Would you give me the link to your article to accept dynamic parameter values?
Thank you very much!
Hey, I would like for excel to get this “parameter” by code, because of this problem:
I have to send a company ID to the procedure so that we filter by company ID, but that ID is in a session when someone runs my program…
how could i send that information on the session to the excel file, so that it sends it to the procedure…any ideas?
Virginia:
I’m finally doing the follow-up article on Wednesday…I promise!
I do this and it works fine, even when I need to pass parameters. But the issues i’m having is that Excel is not arranging my columns in the same manner the Stored Procedure returns them, How can I get Excel(2007) to play nicely? Thanks.
You can all ignore my question. I restarted with a clean slate. Deleted the spreadsheet contents and re-did the Data source and all the columns are now in line with he Stored Procedure results. The issues was with using a previous spreadsheet and modifying the Data Definition, caused Excel to get confused. My Bad…