SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Excel with Stored Procedures


Excel with Stored Procedures

Author
Message
Eric L Hackett
Eric L Hackett
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 211
I use Excel 2003 with stored procedures without involving VBA. The trick is to manually edit the .dqy file that MSQuery generates, or more accurately, just manually create the file and leave out MSQuery altogether. Its just plain text so Notepad works nicely. I personally use UltraEdit.

The format you want is:

XLODBC
1
DSN=myDSN;DATABASE=myDatabase;Trusted_Connection=Yes
EXEC dbo.myStoredProc ?, ?
prompt1 prompt2



The first 2
lines are standard.
Line 3 is basic connection string info.
Line 4 is your EXEC statement with parameters listed as '?'s
Line 5 is a tab-separated list of parameter names, or prompts for Excel

Save the file as myStoredProc.dqy. In Excel, use Import External Data > Import Data, and browse for your new file. Then hit the Parameters button and you'll see your parameter prompts. I usually set them to update on change of a cell value.

I was always frustrated with MSQuery limitations, now I have no need for it. This made things much easier for me and I have built some very nice user interfaces with zero VBA.
katesl
katesl
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 473
The people responsible for architecting the "solutions" for a business should be about the business' data primarily and about the tools/technology secondarily. (I was a statistical data analyst before I became a database programmer.) Use the minimum technology to report the data to the data owners. In many situations, this means Excel is better than adequate and the business has no need for the expense of SSAS or Sharepoint technology or technologists-- even though technology vendors have a need to sell their wares.

The managers who are my customers for Excel "reports" are old hands at "Data - Filter - AutoFilter" and now they use the same Excel they used to grow the business to filter the result sets delivered to Excel by the views.

As the SQL Server DBA **and SQL Server sysadmin** I spend my time on data quality, data integrity, data security-- and on fixing "architecture" devised by network and Windows admins who seem to have some real blind spots about business requirements.

_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
Regina Beauregard
Regina Beauregard
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 167
I have a management background and in my later years learned computers using Access. I started with Access 1.0 the first version Microsoft ever realized and today as a result I build great interfaces using very little code but I use all the bells and whistles that Microsoft provides in this product so I go at things a little different than a DBA and Programmer. That said, I progressed to SQL Server using version 7 and now I use version 2005 and I build databases, tables, multiple step SSIS packages and still use very little code. The VBA example above written by a programmer I'm sure works very well but all I do is the following in the external data query I click on criteria and add the field used for the prompt such as date and in the value I enter a prompt such as [Enter Date]. This works just as well as the VBA code.
magarity kerns
magarity kerns
Mr or Mrs. 500
Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)

Group: General Forum Members
Points: 514 Visits: 397
I love the way the technical spec was written and finished first and then they came to the DBA to ask how/if it can be done. Who else has been there / done that?
Samuel Vella
Samuel Vella
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 2144
magarity kerns (8/13/2008)
I love the way the technical spec was written and finished first and then they came to the DBA to ask how/if it can be done. Who else has been there / done that?


been there multiple times, only done it once
every other time i've told them how it should be done Wink

Still there could be some very valid technical reasons why it had to be done with stored procedures.
And Excel is the defacto tool for data manipulation outside of the database team
Sean Matthews
Sean Matthews
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 217
zahid.m.shafi (8/13/2008)

...Also I believe many technologists [aka developers] really don't want to spend their lives building reports that half competent users should really be able to do for themselves and are nearly always (similar to the VB macro universe) just slight variations on a couple of basic themes.
{ Obiviously I appreciate that most of us earn our money impressing ourselves by parameterising these 2 or 3 very basic patterns so ...er ...well :-) }...


Amen.
Bradley Deem
Bradley Deem
Say Hey Kid
Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)

Group: General Forum Members
Points: 663 Visits: 1248
This seems to be the proper audience for the following scenario so here it goes...

We have multiple databases that each have multiple reports for the client delivered in an Excel format.

The databases are OLTP with no plans for a Data Warehouse any time soon. Data is pulled using Stored Procedures in SSMS and then copy/paste to Excel. Then Pivot tables present this the data to the end user.

There are nearly 200 Reports that need to be updated weekly for delivery to the client. Many of those reports take multiple stored procedures that may take any where from 30seconds to 20 minutes to run. Many of the stored procedures cannot run concurrently due to contension issues, thus they are all run in serial.


Currently, the plan is to develop custom software that can Schedule the reports. Run the stored procedures and copy them into Excel automatically. A prototype has been developed.


Would that be the recommend solution or something else?
Philippe Cand
Philippe Cand
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 294
Samuel Vella (8/13/2008)
David,

If you're using reporting services and users have web access to run reports then reports can easily be integrated into Excel (not sure about Excel 2000) using a web query.


Hi, This sounds like a great idea. I tried a couple of these from the simplest to the most complex, using either the Reports or the ReportServer access.
Well, If my reports takes parameters, I cannot see how to change them after the first query ran. Also, I shall select only the data table so If I edit this query later I get errors because other objects are missing, also the resulting query looks to refer to an execution ID or snapshot, not to a fresh query. Any idea how to make this a little more dynamic? bellow is an example of original query I tried;

http://smdev.ad.onsemi.com/ReportServer/Pages/ReportViewer.aspx?%2fDistribution+Resale+to+End+Customer+Management%2fPOS+Lookup&rs%3aCommand=Render&CustCd=%23%23%23%23%23&Cust_Name=%&Part=1N4004&Region=AP&Region=EUR&Region=CHN&Region=JPN&Region=AMR

BI Guy
stevensonk
stevensonk
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 16
I have created many queries using Excel as my tool. Instead of stored procedures I use Views because they allow parameters. create a view in SQL and in the SQL box in EXcel you would put all the fields that you will be selecting from your View and then you can paramaterize. It works great!
aureolin
aureolin
SSChasing Mays
SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)

Group: General Forum Members
Points: 615 Visits: 1051
I do realize that this is just asking for trouble, but how do you do the same thing in Access? BigGrin

Steve G.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search