You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s author is Mike Alexander, an Excel MVP who shows us how to run a Stored Procedure to get data from a SQL server. For more useful articles and videos, visit www.datapigtechnologies.com.
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. I'll do a post on this later this week.
· 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.
Comments: (4) Collapse
You can actually use Dynamic Parameters withOUT VBA.
You probably already know this and keep it for a later post.
Anyway, I thought I'd put this info here.
In step 7, the picture shows a disabled button 'Parameters'; you can have access to it if you start a bit differently:
-Follow the same process but in step 1, choose 'From Microsoft Query' instead of 'From SQL Server'. Go through all the steps as above.
-In step5, replace the sql by the stored proc:
Exec SP_GetEmployeeManagers ?
The question mark (?) will be understood as a parameter.
Click ok
The query will refresh and a popup window will ask for the parameter value. Enter a value and click ok.
- Now go back to the ribbon > External Data > Properties > 'finger' icon > the Parameters button is now enabled. Click it to set your parameter(s). You have the choices to:
- get a prompt at each refresh for each param
- use a constant value
- or link the parameters to cells (and even auto-refresh when a cell is changed).
How would you use MS Query with a parameter taling its value from a cell?
Zee, once you reach the "the Parameters button is now enabled." step in my post and go to set it up, the 3 choices i described above will be given to you in the Dialog, and one of them is to tie a cell to the parameter (you'll use the RefEdit box to enter the cell reference).
Note: in Step 5, instead of using a stored proc, you could just enter a regular SELECT sql statement. Same thing, use ? as the parameter placeholders. Eg:
SELECT Zipcode, SUM(Amount) Amt FROM Sales WHERE City=?
Then, when you get to the Parameters steps, set the City parameter.
To make it easier, you could 1) use a regular sql, using : City='Portland'
Then 2) make sure it works. And finally, 3) replace the filter by: City=?
Then set the parameter.
Where can I find the next post you had indicated you were going to do where Parameters can be used?
I seems silly that the parameters don't come up in the first place.
Your directions were pretty clear and was able to get this static data pull running. Thanks!
Thanks for the direction on the posts but it fell short of a defining how params need to be handled. I did figure it out however so thanks.
I hate saying this but office 2010 still needs work, where is the forthought for ease of use for power users
who are aways swamped with work and reaching out to MS to make things more intuitive. It seems they go
so far in a new project then fall short of cleaning it up before release. Well there is my rant. LOL
Comments: (loading) Collapse