Excel with Stored Procedures

Question

Excel with Stored Procedures

Viewing 15 posts - 61 through 75 (of 120 total)

You must be logged in to reply to this topic. Login to reply

Korey Ewing SSC Veteran Points: 207 More actions · Answer 1

I my experience the syntax that as consistently worked is:

{Call storedprocedure (?,?)}

So the parameters had to be in parentheses and the entire statement had to be preceded with Call and enclosed in curly brackets.

Not sure if that is what everyone will find but that has consistently worked for me.

exec and ? haven't worked for some reason.

Old SQL Newbie Ten Centuries Points: 1060 More actions · Answer 2

Korey, are you doing this successfully on Excel 2007? If so then I need to figure out why mine doesn't work.

Thanks!

Korey Ewing SSC Veteran Points: 207 More actions · Answer 3

And we got the cell reference to work in 2007 through the parameter dialog not by entering the direct cell reference. I think the SQL you enter goes straight to the database, so it won't get any outside references.

Old SQL Newbie Ten Centuries Points: 1060 More actions · Answer 4

You mean, you are answering the ? prompt by typing in the cell address where the actual parameter value is?

Richard Ray-222778 SSC-Addicted Points: 491 More actions · Answer 5

I do this in dozens of spreadsheets for users all over the resort in both Excel 2003 and Excel 2007. Use MSQuery. Cancel for tables but tell it you want to continue. Go to the manual SQL entry dialog in MS Query and use exactly this syntax; it is the only way it works.

{Call dbo.sp_myproc(?,?)}

The question marks match the parms in the sp. You may hardcode a parm here but use the question mark for others. I don't know that the schema designator is required, but I've always used it.

When you close the SQL editor in MS Query you will be warned that the query cannot be displayed graphically - that's OK, you don't need it.

A dialog box comes up asking for the parm values. Leave it blank and OK through it for each parm for which you entered a question mark. When you're done you should see the column headers that your sp returns, but no data.

Now go to the File menu in MS Query and select Return Data to Microsoft Excel. When you get to the worksheet you'll get a dialog box that asks where you want to put the data: select the cell where the upper left corner of the data should live. Don't close the dialog, you'll still need it to indicate where the input values for the parms in your sp should come from.

Now click the Properties button in that dialog, then select the Definitions tab. At the bottom of that dialog there's a button for Parameters. Select that. In the following dialog use the bottom radio button labeled 'Get the value from the following cell', and fr each one click on the cell in the spreadsheet from which you want to get the parm value. You may also type in the reference, but clicking has been a better choice for me to make certain I get the right cell entered correctly. At this point you may also choose the make the query execution interactive by checking the 'Refresh automatically...' button, but my use tends to be for queries that require multiple parms and take awhile to execute, so I've rarely used that.

Click OK until you get back to Excel. Voila, data. Change a value in one of your parameter input cells and use the Refresh button in the Data tab on the silly ribbon to update the data. For users with really limited skills I sometimes add a button with a recorded macro to refresh the data, but I try to avoid that so that all the spreadsheets where I use this technique are consistent.

Old SQL Newbie Ten Centuries Points: 1060 More actions · Answer 6

Don't close the dialog, you'll still need it to indicate where the input values for the parms in your sp should come from.
Now click the Properties button in that dialog,

This is where I messed up, I didn't see the properties button. It isn't there (or I don't recognize it ?) when you get back into the properties of an existing MSQuery connection, so I'll create a new one and be more observant as I proceed.

Thank you for the details, I think this is going to be very helpful. 🙂

Old SQL Newbie Ten Centuries Points: 1060 More actions · Answer 7

I got it working, thanks!

Funny thing, in the previous spreadsheet that I created and blew past the place where you indicate where teh input values for the parms should come from, when you go back into the connection properties, the parameters button is dimmed - so dim that I never noticed it. It appears I can't find a way to go back and add a parameter after missing the first opportunity.

In the new one where I created the parameter while creating the connection, I can go back in to the connection properties, the parameters button is available, and I could change the Call statement. Interesting.

Anyway, now my data connection works and someone in finance is going to be very happy to get this information. (If I get a bonus, you get half. HaHa)

Thank you again for the detailed explanation - Happy Friday!

Eric L Hackett SSC Eights! Points: 978 More actions · Answer 8

That sort of inconsistent behavior in MSQuery is exactly why I edit my .dqy files manually as described early on in this thread.

david0044 SSC Veteran Points: 206 More actions · Answer 9

Thanks for the suggestion. Everything works fine for me until I go to click the final OK to import the data. Then I get the Invalid Parameter Number error and the import fails without returning any data.

I'm not sure if its related, but when I try the MSQuery>Execute SQL dialog box and try to paste the SP from the Procedures button, I get {Call proc_exceltest(@RETURN_VALUE, @var1)}. My SP only has one parameter @var1.

Thanks,

David

Old SQL Newbie Ten Centuries Points: 1060 More actions · Answer 10

Eric, I just went back and re-read that post of yours. The day you posted it, it was totally over my head. Today, I get it. So I searched my c: drive for *dqy, no results. I searched My Documents, Excel Help, googled, and MSDN. I looked in my options for a default file location. I can't find my *dqy files. I should have at least three of them. How do you find yours to edit them?

Thanks!

Eric L Hackett SSC Eights! Points: 978 More actions · Answer 11

Excel 2003 by default puts them in My Documents > My DataSources. At least on my machine, but that could be an option on our corporate image. I just create my own with a text editor and store them where I choose. Click Data > Import External Data > Import Data to find out where it opens up by default.

One thing worth mentioning, is that once you've 'imported' the .dqy file, Excel somehow embeds the data definition, meaning it is no longer tied to the file. Editing the file will not change the query already used in Excel. If I have to change the query, I usually start with a fresh .xls file.

Old SQL Newbie Ten Centuries Points: 1060 More actions · Answer 12

I looked in the place in Excel you suggested to find where Excel is storing connections.

C:\Program Files\Common Files\ODBC\Data Sources

There I find dsn files that I can't edit. One of them has the name of the file that I created by following your directions this morning. Could it be because I'm connecting to a data warehouse using Microsoft Query that it's creating a dsn instead of dqy? That doesn't explain why I can't find 1 file for every spreadsheet I have created. Are they all using the same dsn and storing the other information somewhere else?

On the network in myname\my documents\My Data Sources I'm finding *.odc files, but not one for every spreadsheet I'm creating.

In the Excel 2007 spreadsheet itself, in the properties I find these entries:

In the connection String Box:

Description=StreckDW;DRIVER=SQL Server;SERVER=[MyServerName;UID=[MyName];APP=Microsoft Data Access Components;WSID=[MeAgain];Trusted_Connection=Yes

In the Command Text box:

{Call dbo.usp_MyStoredProc (?,?)}

At least I know how to create now, but not how to find it to edit it. 🙁

mmalik SSC Rookie Points: 29 More actions · Answer 13

I have a problem using this .dqy file method...it works when I have stored procedure with one parameter but does not works when I have to pass multiple parameters...e.g. I have stored procedure with 2 parameters year, month. As soon as I tab out of one cell (after entering year), query starts running without waiting for other vlaue ...please help.

Eric L Hackett SSC Eights! Points: 978 More actions · Answer 14

You would need to uncheck the auto-refresh option on your parameters dialog, and use the Refresh All button after changing both parameters. I add this button to my standard toolbar for convenience.

mmalik SSC Rookie Points: 29 More actions · Answer 15

where I can find "Refresh All Buttons" option...can you please help me here....