How do I generate a .txt file from a Stored Procedure.
Something like this:

select * from my_table
into c:\temp\my_text_file.txt

Thanks in advance for any help.

Sandra

There isn't anything to directly do this (e.g.nothing like
an option for stored procedures results to text) but there
are several ways to accomplish this.
Using the command line utilities, you could call xp_cmdshell
and use osql to output the query results to a file. Along
the same lines with xp_cmdshell, you could use bcp out.
You could write an extended stored procedure to do this -
and a variety of methods in the xp to do this (ado and file
system object, etc.)
DTS is probably the easiest method though - just use a text
file destination and a data pump to drop the query results
to a text file. And you could call the package from a stored
procedure using dtsrun (or sp_OA procedures).
You could have a combination of ADO and FileSystemObject and
call this in other ways than just an extended stored
procedure - script file, external app, etc.
And as I type I keep thinking of a lot of ways to do it
along these lines. It really depends more on where you are
calling the stored procedure from.

-Sue

On Tue, 30 Sep 2003 15:31:58 -0700, "Sandra"
<silvah (AT) wellsfargo (DOT) com> wrote:

In News-Entry:0d4001c387a2$a92ce7d0$a101280a (AT) phx (DOT) gbl,
Sandra <silvah (AT) wellsfargo (DOT) com> wrote:
Use BCP.
The follwing line put the content of tbl_test into tbl_test.txt (in
DOS-Console)

bcp "MyDBName.MyDBUsername.tbl_test" out
"c:\tbl_test.txt" -c -q -U"MYDBUsername" -P"MyDBPassword"
If you want to use this string in a sproc you have to quote the command with
' and start it with xp_cmdshell

master.dbo.xp_cmdshell 'bcp "MyDBName.MyDBUsername.tbl_test" out
"c:\tbl_test.txt" -c -q -U"MYDBUsername" -"MyDBPassword"'


usage: BCP {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-6 6x file format] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]


kind regards

Frank
www.xax.de