unlikelyloginname
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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