Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using COM to Output Queries to a Text File

By Les Smith,

Using COM to Output Queries to a Text File

Purpose 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:

  1. It keeps the Active Server Page simpler. Stored procedures are the only interface the ASP programmer needs to use. Additional complexity is avoided.
  2. Several web sites could use the same stored procedure. Otherwise, one would need to register the com object on each web site and keep each web server updated with the latest version. Thus deploying and maintenance are simpler.
  3. Com objects instantiated by ASP are annoyingly over persistent. After an ASP page instantiates the object it is never released, and the DLL becomes locked. At the point one needs to stop the web server, and sometimes even reboot the machine. The sp_OAdestroy system stored procedure seems to do a nice job of destroying the object. One might expect that in ASP the command ‘set obj = nothing’ to do the same, but it does not. According to Microsoft this behavior is by design. See http://support.microsoft.com/support/kb/articles/Q200/2/60.ASP
The article mentions this as an issue for Personal Web Server, but this also applies to IIS.

Calling Method: sp_exportData 'sa','sa',"select * from Suppliers", "Suppliers.txt", 'Northwind'

Download:


Return to Les Smith's Home

 

Total article views: 6556 | Views in the last 30 days: 3
 
Related Articles
FORUM

Creating view from stored procedure results

Creating view from stored procedure results

ARTICLE

Creating Stored Procedures - SQL School Video

MVP Brian Knight covers the basics of creating stored procedures in SQL Server. Stored Procedures ar...

FORUM

To Create PDF from SQL stored procedure

To Create PDF from SQL stored procedure

Tags
advanced querying    
programming    
t-sql    
visual basic 6    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones