Larry - not directly.
Quote:> Is there any way to send a result set to a text file directly from
> T-SQL? I know that you can from osql pretty easily, but what about from
> QA? or for that matter from a sproc/xsproc? Of course if I knew how to
> put results into a variable, then we could use xp_cmdshell, right?
Q. How can I read/write to a flat file from inside a SQL Server TSQL
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.
6. There is a free XP - xp_query_to_file - at www.dbmaint.com
Neil Pike MVP/MCSE. Protech Computing Ltd
(Please reply only to newsgroups)
SQL FAQ (412 entries) see
or www.ntfaq.com/sql.html (+ ntfaq download)