Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Excel with Stored Procedures Expand / Collapse
Author
Message
Posted Wednesday, August 13, 2008 7:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:44 PM
Points: 69, Visits: 205
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.
Post #551852
Posted Wednesday, August 13, 2008 7:16 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 16, 2014 7:20 PM
Points: 63, Visits: 470
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.."
Post #551862
Posted Wednesday, August 13, 2008 7:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 5, 2013 8:12 AM
Points: 34, 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.





Post #551878
Posted Wednesday, August 13, 2008 8:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 8, 2014 10:08 PM
Points: 358, 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?
Post #551987
Posted Wednesday, August 13, 2008 9:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:23 AM
Points: 328, Visits: 1,999
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 ;)

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
Post #552020
Posted Wednesday, August 13, 2008 9:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 8:04 AM
Points: 208, Visits: 206
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.
Post #552043
Posted Wednesday, August 13, 2008 10:04 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 9, 2014 6:19 AM
Points: 554, Visits: 1,199
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?
Post #552056
Posted Wednesday, August 13, 2008 10:07 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:10 PM
Points: 49, Visits: 279
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
Post #552061
Posted Wednesday, August 13, 2008 11:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 22, 2011 12:37 PM
Points: 2, Visits: 14
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!
Post #552117
Posted Wednesday, August 13, 2008 11:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 26, 2014 12:13 PM
Points: 498, Visits: 977
I do realize that this is just asking for trouble, but how do you do the same thing in Access? :D

Steve G.



Post #552129
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse