Excel with Stored Procedures
"http://www.sqlservercentral.com/articles/Stored+Procedures/63537/
Printed 2008/12/03 07:16AM
Excel with Stored Procedures
By David Poole, 2008/06/25
Excel with SQL Stored Procs
Introduction
A colleague asked how I felt about making a user a member of db_datareader on a server used by non-DBAs for reporting data. The server exists precisely to allow people to write ad-hoc queries without threatening live systems.
He then went on to say that they were using Microsoft Excel to query the data and that this was in the requirements for the project he was working on. He was asking the question because his technical specification said that Excel would query the data by calling stored procedures but the end user said that this wasn't possible without writing reams of VBA macros.
Using Microsoft Excel is a bit off my radar but I thought I would investigate. I'm using Excel 2000, it works, I'm comfortable with it, it does everything I want so why upgrade?
Basic queries with Excel
SQL queries are classed as external data so our first port of call is to use the commands on the Data menu as shown below.
Choosing this option will then present you with a dialogue box that will allow you to set up a new ODBC connection or use an existing one.
I set up my AW data source to look at the AdventureWorks database so I chose that to start my experiments.
This takes you into a wizard that only shows you objects owned by dbo and of course Adventureworks has most of its objects placed on other schemas so I clicked cancel and got a popup dialogue asking 'Do you want to continue editing this query in Microsoft Query' to which I answered YES.
On doing so the Microsoft Query application will open with the dialogue to select the tables. The Owner d"
Printed 2008/12/03 07:16AM
Excel with Stored Procedures
By David Poole, 2008/06/25
Excel with SQL Stored Procs
Introduction
A colleague asked how I felt about making a user a member of db_datareader on a server used by non-DBAs for reporting data. The server exists precisely to allow people to write ad-hoc queries without threatening live systems.
He then went on to say that they were using Microsoft Excel to query the data and that this was in the requirements for the project he was working on. He was asking the question because his technical specification said that Excel would query the data by calling stored procedures but the end user said that this wasn't possible without writing reams of VBA macros.
Using Microsoft Excel is a bit off my radar but I thought I would investigate. I'm using Excel 2000, it works, I'm comfortable with it, it does everything I want so why upgrade?
Basic queries with Excel
SQL queries are classed as external data so our first port of call is to use the commands on the Data menu as shown below.
Choosing this option will then present you with a dialogue box that will allow you to set up a new ODBC connection or use an existing one.
I set up my AW data source to look at the AdventureWorks database so I chose that to start my experiments.
This takes you into a wizard that only shows you objects owned by dbo and of course Adventureworks has most of its objects placed on other schemas so I clicked cancel and got a popup dialogue asking 'Do you want to continue editing this query in Microsoft Query' to which I answered YES.
On doing so the Microsoft Query application will open with the dialogue to select the tables. The Owner d"