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 Tuesday, August 12, 2008 9:34 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:34 AM
Points: 2,904, Visits: 1,822
Comments posted to this topic are about the item Excel with Stored Procedures

LinkedIn Profile
Newbie on www.simple-talk.com
Post #551557
Posted Tuesday, August 12, 2008 10:07 PM


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
I think using Excel as the interface for selects from SQL Server is so much better than programming a dinky new interface. I'll save this article as a guide if I want to make it possible for the Excel user to enter a parameter, so thank you.

My usual practice is to make the first sheet in an Excel workbook the "refresh data" sheet -- no parameters-- just using the enabled "refresh data" button built into Excel which runs "select * from viewName". A second sheet in the workbook presents and comments on the query (view) that selects the data -- this is rudimentary data dictionary for data owners; documents the database permission that the "refresh data" requires; and states a "sunset date" for support of the view. As the DBA, I keep a record of the distribution of these "refesh data" sheets and the scheme of database permissions.

The great thing about the use of Excel with SQL Server is the Excel's default behavior of putting the column headers from the (non-parameterized, select *) query in row 1, and keeping the "freeze panes" property set on the sheet, so row 1 is always in view as the header. So far, I have had no performance problems -- because I maintain the indexes and statistics on the tables from which the view selects -- and I observe an informal limit of about 20000 records to be returned by a view selected from Excel. I've arrived at this limit from listening to data owners' comments on the usefulness of the "reports" more so than having to admin query performance. Some of the views have where clauses using date functions with getdate() to select only recently created or updated records.



_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
Post #551569
Posted Wednesday, August 13, 2008 12:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:04 PM
Points: 5, Visits: 49
Hello,
I just wanted to note that--at least in Excel 2007--you can use the call syntax to call stored with parameters from Excel.
{Call ?,?,?,?,... ?} works just fine.
You're warned that the query can't be displayed graphically and that you can't use parameters--but it still works. You're prompted for parameters, you enter them, then you return to your worksheet and you're able to specify the cells (or hard coded values) that feed the ?s in your Call statement.

Post #551613
Posted Wednesday, August 13, 2008 5:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 27, 2011 9:43 AM
Points: 2, Visits: 17
Was hoping for a lot more in this article, I think this subject touches on one of the main reasons we use SQLServer ie to just get data into Excel like user interfaces - with flexible reporting functions.

As you mature this idea ( i.e. MS Office tools needing database/stored proc access, which BTW has been ongoing for probably 10years+) you eventually starting talking about Analysis Services/Sharepoint & Live Services and hence I think stored procs are not the way forward because much *bespoke* coding [function of client AND server side] is what it eventually results in...

[ In fact may I be so contentious as to say that SQLServer DBA jobs only exist today to facilitate things like Excel data sourcing ]

...or perhaps you folk think that the SSAS technology is still immature and want to resort to these outdated modes of thinking?

Not having a dig at anyone just want to encourage the move forward rather than backward.

Post #551767
Posted Wednesday, August 13, 2008 5:26 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 been using this for about 10 years for management reports but I don't allow the users to create their own queries because most users are not technical enough to create a query. I create the query and then I have the users refresh the query and it works well. My spreadsheet have multiple worksheets and they use the refresh all feature to update all the supporting spreadsheets. The queries look for the parameters on the main worksheet sometimes in hidden fields. Using this method the user is only prompted once for each parameter. I also use a DNS less connection so they don't have to setup an ODBC driver and I use a generic user on the SQL end. I limit what they can see by creating views that only the generic user can use.:)
Post #551768
Posted Wednesday, August 13, 2008 5:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:56 AM
Points: 2,716, Visits: 2,458
I too have used this technique for many years as a way of quickly exposing the outputs of SPs for reporting - usually reporting that sits outside of the more structured MIS system.

One thing to note is that you might start to see lots of 'new' errors in the logs, along the lines of spid xx DBCC TRACEON 208, SPID XX

These can be suppressed by using trace flag 2505 - more info here : http://support.microsoft.com/kb/243352



Kev
Post #551777
Posted Wednesday, August 13, 2008 5:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:34 AM
Points: 2,904, Visits: 1,822
I've got to keep 200 DB servers up and running with typical specs of 16 CPU 24GB RAM replicating across 3 sites.

Even at my most cynical (and I can be more cynical than most) I wouldn't describe my job as facilitating Excel.

I agree that Analysis Services should be utilised more. If anyone knows how to integrate cubes with reporting services I would be very interested.

The problem with ad-hoc queries is do you dare let someone run an ad-hoc query on a multi terrabyte database albeit a reporting server. Great fun fielding angry customers complaints that they can't generate their reports because someone ran a query with a dodgy join and non-existent of crazy WHERE clause in it. The last time I saw a reference to that much OR was in King Solomons Mines.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #551786
Posted Wednesday, August 13, 2008 5:55 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
Thanks for the information but I do not use stored procedures and I'm wondering why you feel this is necessary. I base the spreadsheet on specific views and then I allow parameters which I code into the view such as a date field, I use [Enter Date] in the criteria field. This eliminates problems with SQL Server.
Post #551787
Posted Wednesday, August 13, 2008 6:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 6:18 AM
Points: 337, Visits: 1,995
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.
This removes the need for stored procedures and gives the advantage of formatting coming over with the data.

As far as integrating SSAS with SSRS then this can be done, although my experience with this runs as far as building a proof of concept for a client.
The disadvantage of SSAS though is that for smaller systems the Analysis services adds an extra layer of complexity and a requirement of technical knowledge that not all companies (especially the smaller ones) need or want.
Post #551797
Posted Wednesday, August 13, 2008 7:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 27, 2011 9:43 AM
Points: 2, Visits: 17
Continuing with my satanic advocacy and to test my thesis further..."how many of your servers are for 'producing reports' versus "act as prime records for key OLTP processing' ?"
...perhaps the fairest way to answer the question is in terms of 'online data storage' to the nearest gigabyte.

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 }

Finally we really need to get over this self-enforced threat about 'rogue joins', tis a bit like 'the tail wagging the dog' - I'm not denying the issue exists but I dont believe in this day and age that we need to resort to it or advertise it.

Now that v. large databases have been around for a decade plus and there exists a major ongoing demand for MIS/reporting services then we, as technologists, need to focus on solving this problem 'efficiently' - and I mean 'efficiently' in a truly global computing paradigm sense and not local to our day to day lives.

I find it remarkable that a technologist can have luddite tendencies...

{note this subject just interests me, am keen to see discourse on it and I repeat that I really do not mean to offend anyone with my ramblings so apologies if I have }
Post #551845
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse