Using COM to Output Queries to a Text File
April 26, 2000Purpose of script: Create a tab delimited file for an ASP intranet application. The files are to be text versions of static HTML reports that the user can download when an "Excel file" is needed.
We needed to create a method of creating tab delimited files based on the results of an SQL query. Surprisingly, there seemed to be no system stored procedure within SQL Server that could perform this task.
One possibility was using a DTS package. However, I did not see a way to pass parameters to the package for changing queries and the text file target.
So I decided to create a simple com object to export a query to a file. I used Visual Basic to create the class which references ADO and Scripting Runtime. Again, DTS seemed like a possibility. I could create a DTS com object in Visual Basic. However, DTS seemed really too difficult too code, when a simpler API would work just as well.
Another decision is whether to have a stored procedure or an Active Server Page instantiate and use the com object. Having the stored procedure do it seems to be the better choice for the following reasons:
Calling Method: sp_exportData 'sa','sa',"select * from Suppliers", "Suppliers.txt", 'Northwind'