Retrieving Data from SQL Server within Excel 2007

Question

I have the following requirements

• I have relational content stored in a SQL Server 2005 database.
• I want to retrieve this data, transform it and display it in an Excel 2007 document.
• I would like this to be pulled from within Excel at the click of a button.

As far as I can tell I have the following options...

• Create a view in Sql Server and reference it within a pivot table using a data connection.
• Create an Excel workbook using VSTO and create a web service which the workbook pulls data from
• Create a web service and use JScript with XMLHTTP request objects

I currently only have a little experience of any of these methods (I'm very familiar with SQL Server although not pivot tables, I've created many web services although I've never used VSTO and I've used javascript extensively, but never in the context of Excel manipulation).

Would you recommend one of the above approaches or is there a better way to do this?

Answer 1

By far the easiest of your three options is the first.

I have provided this solution to colleagues before and it works well. However, a gotcha is that Excel places a structure level lock on the tables it uses (either directly or via a view) so if you need to make any schema changes while the Excel user has their linked spreadsheet open, you are out of luck.

(I had this issue as they were reviewing an import table which I used to drop/create to improve efficiency)

I would only entertain the second two options (both look do-able) if your users are remote (off lan/vpn)