Recently I have read a lot of Blog posts on how to export files in different formats. But I use a custom procedure which exports text files in Pipe Delimited format using BCP.This procedure can be used in other processes for Output and Error Handling.
Here is the Script for the procedure.You can modify the below procedure using any of the delimiters.
alter procedure Export_To_TextFile ( @OutputQuery varchar(5000) ,@FileLocation varchar(400) ,@FileName varchar(1000) ,@ServerName varchar(100) ) as Declare @bcpcommand varchar(8000) set @bcpcommand = 'bcp "' + @OutputQuery + '" queryout ' + @FileLocation + @FileName + ' ' + CHAR(13) + '-c -t" | " -T -S ' + @ServerName Exec master..xp_cmdshell @bcpcommand
Here is how I call the procedure
