CODEWITHSUNDEEP
sqlsql-server
Joe
Joe

Reputation: 255

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?

Upvotes: 0

Views: 1956

Answers (2)

yakatz
yakatz

Reputation: 2282

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.

Upvotes: 0

Brian Webster
Brian Webster

Reputation: 30875

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.

Upvotes: 1

Related Questions