> I am currently looking for a way to create a ascii text
> file from the result set with ISQL/w commands either within
> the query script or from inside a database procedure.
> Oracle has this ability using 'spool filename'
Q. How can I output records/messages to a flat file from inside a
SQL Server TSQL script/stored-procedure/trigger?
A. SQL Server doesn't have a handy SPOOL command like Oracle does, but
there are a number of ways of doing what you want.
1. 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
3. Write your own extended stored procedure. As this is a C program
it can use standard file access commands to achieve whatever you want.
4. 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