Dumping stored procedure result to a text file programmatically How can I save stored procedure execution result to a text file? I need to do this from SQL script (not Management Studio nor external utility) and I need to preserve correct characters encoding (Unicode in this case).
The simplest method would be a 2 step approach :


Log the output of stored procedure to a physical table in the sql server database. e.g. insert into dbo.X_sp_name_out exec dbo.sp_name
Use BCP OUT or SQLCMD to export data to text file.
(Optional.. but important to manage table growth) Have a purging process to purge out the old records or before next execution of the SP, truncate the table.


Make sure you use N'your unicode data' when storing data.

You can think of using SSIS to do the same and schedule the package using SQL Agent job.