Save output of a SP to a file and create a job to execute it

I 开发者_开发知识库have a SP which returns a XML string as output. I want to save the result in a .xml file automatically when the SP is executed. whats the best way to do that?

First, saving output to file:

exec xp_cmdshell 'bcp "select * from suppliers" queryout "c:\suppliers.txt" -S server -T'

Second, Scheduling a SQL Job

http://msdn.microsoft.com/en-us/library/ms190268.aspx

Alternatively (in case of SQL Express)

• Use command-line SQL to execute the stored procedure from a windows task, scheduled accordingly
• Build a quick .NET application that executes the stored procedure. Then, setup a windows task to run the executable on a schedule.

This might be exactly what you are looking for: http://munishbansal.wordpress.com/2009/02/20/saving-results-of-a-stored-procedure-into-a-xml-file/

In a nutshell, you have four options:

1. Using CLR Stored Procedure.
2. Using Command Line Utility (OSQL).
3. Using xp_CmdShell utility of SQL Server.
4. Creating OLE objects in SQL Server (sp_OACreate).

I will not paste most of that article here, but it is pretty well written.