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, ... .