Excel with Stored Procedures

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David Poole » Excel with Stored Procedures

121 posts, Page 2 of 13

««1 2 3 4 5 »»»

Excel with Stored Procedures

Rate Topic

Display Mode

Topic Options

Author

Message

Eric L Hackett

Posted Wednesday, August 13, 2008 7:07 AM

Valued Member

Group: General Forum Members
Last Login: Friday, March 01, 2013 11:16 AM
Points: 67, Visits: 188

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

katesl

Posted Wednesday, August 13, 2008 7:16 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 23, 2013 6:08 PM
Points: 60, Visits: 406

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

Regina Beauregard

Posted Wednesday, August 13, 2008 7:32 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 2:01 PM
Points: 34, Visits: 166

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

magarity kerns

Posted Wednesday, August 13, 2008 8:51 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 10:22 PM
Points: 358, Visits: 393

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

Samuel Vella

Posted Wednesday, August 13, 2008 9:25 AM

Old Hand

Group: General Forum Members
Last Login: Saturday, June 15, 2013 10:38 AM
Points: 329, Visits: 1,869

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

Sean Matthews

Posted Wednesday, August 13, 2008 9:45 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 9:42 AM
Points: 208, Visits: 180

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 Smile

}...

Amen.

Post #552043

Bradley Deem

Posted Wednesday, August 13, 2008 10:04 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 10:18 AM
Points: 551, Visits: 1,153

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

Philippe Cand

Posted Wednesday, August 13, 2008 10:07 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 11:05 AM
Points: 48, Visits: 268

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

stevensonk

Posted Wednesday, August 13, 2008 11:08 AM

Forum 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

aureolin

Posted Wednesday, August 13, 2008 11:23 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, June 10, 2013 10:30 AM
Points: 494, Visits: 947

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 »

121 posts, Page 2 of 13

««1 2 3 4 5 »»»

Permissions