We help IT Professionals succeed at work.

SQL Server Ad Hoc Query Output to Excel (OpenDataSource/OpenRowSet)

G'day!

     I'm working on an ASP application that builds a query on the fly and drops the result into a temporary table in SQL Server 2000.  From there I'd like to move the table to an Excel spreadsheet.  It looks like   OpenRowSet   or   OpenDataSource   ought to be able to do what I want, but I can't seem to make the leap from Microsoft's "documentation" to something that works.  (I have found examples that let me SELECT from an Excel spreadsheet, but none that work for INSERT to a spreadsheet.)  The solution should take the following points into account:

1. The query is built on the fly and produces a variable assortment of columns.  (This makes using a DTS package painful as it, too, would need to be built on the fly.)

2. The name of the Excel spreadsheet file is a variable.

3. Multiple instances ought not interfere with one another.  (I think that (2) and (3) rule out using a linked data source.)

4. The solution should be something that can be integrated into an application rather than requiring "manual" steps, e.g. using the wizard to create a DTS package is not acceptable.

5. The ideal solution would be easy to extend so as to write output in additional formats, e.g. Access, CSV text files, ... .

     Thanks
Comment
Watch Question

David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

Why not use Excel & MS Query to retrieve the results from the temporary table.

While a variable number of columns would be difficult, could you use a standard set of coloumns, some of which may be null?

The data can be refreshed in Excel with one or two clicks.

Other than that you could write something in VBA (macro) that would get the data and populate a blank worksheet.

If you need to I could dreg up some VBA code later today.

Cheers
  David
CERTIFIED EXPERT
Commented:
I think you need o compile an ActiveX component that encapsulates methods that create Excel file with needed formatting and structure. Then you could invoke and use this component from within sp_OA...
sp_OA... is fully thread safe and scalable framework.
As for insert into existing Excel document you need soemthing like this

EXEC sp_dropserver 'Excel', 'droplogins'
exec sp_addlinkedserver N'Excel', N'Jet 4.0',
                   N'Microsoft.Jet.OLEDB.4.0',
                   N'F:\usr\local\src\sql\<excel_worksheet_name>.XLS', NULL, N'Excel 8.0'
GO

sp_addlinkedsrvlogin N'Excel', false, 'sa', N'ADMIN', NULL
GO

Insert into Excel...['<excel_worksheet_name>$'] select * from t

Author

Commented:
    Microsoft giveth, and taketh away.  Something rather like this "works":

exec sp_addlinkedserver N'Excel', N'Jet 4.0',
  N'Microsoft.Jet.OLEDB.4.0',
  N'C:\Results.xls', NULL, N'Excel 8.0'
GO
sp_addlinkedsrvlogin N'Excel', false, 'sa', N'ADMIN', NULL
GO
insert into Excel...[Sheet1$] values ( 4, 8, 'd' )
select * from Excel...[Sheet1$]
GO
exec sp_dropserver 'Excel', 'droplogins'
GO

     This demonstrates that SQL Server can SELECT and INSERT using Excel.  Sadly, it seems that the spreadsheet must already contain a properly formatted table since ALTER statements cannot be executed against linked data sources.  (Curiously, selecting from uninitialized Sheet2 returns a table with a single column named "F1" and a single row containing a null.)

     It looks like I'll need to something ugly in the ASP page to massage the spreadsheet and load the data.  And survive the file caching features of IIS that seem to leave the spreadsheet locked.

     Thanks to miron for getting me as close as SQL Server is likely to get.  Sorry dtodd, but I'm trying to avoid having the user explicitly connecting to the database to pull data out.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.