Generating a text file automatically using SQL Server 2008

Question

I want to execute a Stored procedure in SQL Server 2008 and store the data in a text file. How to do this and how to generate a text file automatically with data in SQL Server 2008?

Answer 1

One of the method is to make use of BCP (Bulk Copy Program) to do this. You need to have xp_cmdshell enabled to get this working.

Also you can also make use of SSIS Import/Export wizard to achieve this.

EXEC master..xp_cmdshell 'BCP "exec dbname.schemaname.procname" queryout "C:\outputfile.txt" -T -c -t,'

Check out these:

1. BCP
2. SSIS