"Go" SQL Server T-SQL Write text file

Original: http://www.nigelrivett.net/SQLTsql/WriteTextFile.html

The is several methods of creating text files from SQL Server.

• osql
• Bcp
• Redirection commands
• Sp_makewebtask
• Com Object Calls

Remember that in all cases the path of the output file is relative to the server-not to the client on which the app Lication is running.

osql.
This example would just output master: sysobjects to the file c:\osqloutput.txt.

Declare @cmd varchar (+) Select @cmd = ' osql-u-p-s-Q "select * from Master. sysobjects "-O" c:\osqloutput.txt "-w500'exec@cmd

Bcp
BCP very fast and easy-to-use for just dumping a table out-can be used against a view too.

"'

Redirection commands
You'll need to create the data to is output as in dynamic SQL statements
The first command here creates or overwrites the File-the rest append to the file.

exec ' echo Hello > C:\file.txt ' exec ' Echo appended data >> C:\file.txt ' exec ' echo More data >> c:\file.txt '

Would generate a file containing
Hello
Appended data
More data

Sp_makewebtask
Creating HTML code from a query

Com Object Calls
Using sp_OA ... system stored procedures and creating COM objects or existing applications you can probably does WHATEVR you Wish-but should probably ask whether SQL Server is the right place to control this.

"Go" SQL Server T-SQL Write text file