//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Other Articles :: How do I send a database query to a text file?


How do I send a database query to a text file?

SQL Server 
 
You can use bcp and xp_cmdshell, assuming the user who needs to run this query has sufficient privileges. Here is a way to send the results of a stored procedure to a file: 
 
DECLARE @cmd VARCHAR(2048) 
 
SET @cmd = 'bcp "EXEC pubs..reptq1" queryout' 
    +' "c:\query.txt" -c -Usa -Ppassword' 
    +' -SLOCALHOST"' 
 
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
 
Another option is to send the contents of a table to a file, e.g. 
 
DECLARE @cmd VARCHAR(2048) 
 
SET @cmd = 'bcp pubs.dbo.authors out' 
    + ' c:\authors.txt -c -t' 
    + ' -SLOCALHOST -Usa -Ppassword' 
 
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
 
If you are using Windows Authentication, and not SQL Server Authentication, there would be one minor change: 
 
DECLARE @cmd VARCHAR(2048) 
 
SET @cmd = 'bcp pubs.dbo.authors out' 
    + ' c:\authors.txt -c -t ' 
    + ' -SLOCALHOST -E
 
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
 
Alternatively, you can use OSQL to achieve the same kind of things, though the syntax is slightly different: 
 
DECLARE @cmd VARCHAR(2048) 
 
SET @cmd = 'OSQL -SLOCALHOST -dPubs ' 
    + ' -Usa -Ppassword' 
    + ' -Q"SELECT * FROM Authors"' 
    + ' -oc:\authors.txt' 
 
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
 
And again, if you are using Windows Authentication, there is a slight change: 
 
DECLARE @cmd VARCHAR(2048) 
 
SET @cmd = 'OSQL -SLOCALHOST -dPubs -E
    + ' -Q"SELECT * FROM Authors"' 
    + ' -oc:\authors.txt' 
 
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
 
If this is something I need automated, I usually create the stored procedure in master, and create a job (owned by SA) — and either schedule it using SQL Server Agent, or start the job manually. This eliminates the need to worry about who is calling it manually and to make sure to set up permissions correctly for everyone who might ever want to call it... 
 
See Books Online for more information and command line switches for the bcp and osql utilities. 
 
SQL Server or Microsoft Access 
 
You can also handle saving a query to a file using ASP and FileSystemObject, though I strongly recommend avoiding this approach if possible. If your result set is going to be more than a few thousand lines, this will cause serious memory issues on the server running the ASP page. (You could experiment with looping through the resultset and calling writeline() for every iteration of the loop, as it may represent less of a burden than drawing the entire set into GetString() — string concatenation is not a strength of VBScript, and GetString() is just a fancy way of saying, "loop through all the rows, and concatenate them as you go." It's more efficient than that, but you get the gist.) 
 
This example creates a CSV file and links to it. So, we start with this table: 
 
CREATE TABLE blat(foo INT, bar VARCHAR(32)) 
GO 
 
INSERT blat(foo, bar) VALUES(1, 'foo') 
INSERT blat(foo, bar) VALUES(2, 'bar') 
INSERT blat(foo, bar) VALUES(3, 'foobar') 
GO
 
Now, ignoring the other enhancements we would make to this script (e.g. creating a stored procedure in SQL Server), we can see that the following will generate a CSV string and place it in a file. 
 
<% 
    set conn = CreateObject("ADODB.Connection") 
    conn.open "<connection string>" 
     
    set rs = conn.execute("SELECT foo, bar FROM blat") 
    csvText = rs.getString(2,,",",VBCrLf) 
    rs.close: set rs = nothing 
    conn.close: set conn = nothing 
 
    set fso = CreateObject("Scripting.FileSystemObject") 
    set fs = fso.CreateTextFile(Server.MapPath("csv.csv"), true) 
    fs.writeline(csvText) 
    fs.close: set fs = nothing 
    set fso = nothing 
 
    response.write "Right-click to download: " & _ 
        "<a href=csv.csv>csv.csv</a>" 
%>
 
You would probably want to use timestamp, random numbers, GUIDs, etc. to name new files as they are created, if you need to avoid overwriting previous versions.

Related Articles

How do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing <text>?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for my character-based database columns?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?
Where can I get basic info about using stored procedures?

 

 


Created: 10/28/2003 | Last Updated: 11/7/2003 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (199)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...