Results to a text file.

Results to a text file.

Post by Gary » Wed, 21 Apr 1999 04:00:00



I have read several threads that include the statement  "pipe it out to a
text file".
I would like to send the results of a select statement to a text file.

does anyone know an good way to do this?

Thank you in advance

Gary

 
 
 

Results to a text file.

Post by Brian K. Lawto » Wed, 21 Apr 1999 04:00:00


From a command line, run ISQL using the -o option.  This redirects all
results to an output file.  For the full set of options and parameters, type
ISQL /?

________________________________________________________

RDA Consultants Limited (http://www.RDAConsultants.Com)
Contributing Editor, SQL Server Magazine (http://www.sqlmag.com)


Quote:> I have read several threads that include the statement  "pipe it out to a
> text file".
> I would like to send the results of a select statement to a text file.

> does anyone know an good way to do this?

> Thank you in advance

> Gary


 
 
 

Results to a text file.

Post by Neil Pik » Thu, 22 Apr 1999 04:00:00


Gary,

Quote:> I have read several threads that include the statement  "pipe it out to a
> text file".
> I would like to send the results of a select statement to a text file.

Q.     How can I read/write to a flat file from inside a SQL Server TSQL
script/stored-procedure/trigger?
(v1.3 1999.04.10)

A. SQL Server doesn't have a handy SPOOL command like Oracle does for writing
to files, but there are a number of ways of doing what you want.

1.  For reading, use xp_cmdshell and the ECHO command.  Use the > or >>
redirection symbols to either create or append to a file.

2.  Put the information you want into a table (note this can't be an ordinary
temporary table, but it can be a global temporary table) and then bcp it out to
a file via xp_cmdshell.
   xp_cmdshell "bcp <dbname>..<tablename> out c:\file.fil -Usa -P<password> -c"

3.  BCP or BULK INSERT (SQL 7 only) can also be used to read in a flat file
into a table, from where it can be processed.

4.  Write your own extended stored procedure.  As this is a C program it can
use standard file access commands to achieve whatever you want.

5.  Run the select through ISQL via xp_cmdshell and use the -o parameter to
output the results to a file.  This example uses the -E parameter to avoild
hard-coding a userid.




 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)
 For SQL FAQ entries see
 http://go.compuserve.com/sqlserver (library 1) - latest stuff is always here
 www.ntfaq.com/sql.html
 http://www.swynk.com/faq/sql/sqlserverfaq.asp