Automate TSQL Query Output
I want to have a stored procedure run on a schedule that automatically creates a database output file as a result and saves it to a specific location on my network. Can someone please tell me how to do this?
Software/Hardware used:
SQL Server 2005
Software/Hardware used:
SQL Server 2005
ASKED:
March 22, 2010 1:14 PM
UPDATED:
June 1, 2010 6:29 PM
Answer Wiki:
One option would be to create a stored procedure using <a href="http://msdn.microsoft.com/en-us/library/ms175046(v=SQL.90).aspx">xp_cmdshell</a> (which is disabled by default) to call <a href="http://msdn.microsoft.com/en-us/library/ms162802.aspx">bcp</a>, and schedule a task to execute it.
Something like this:
<pre>CREATE PROCEDURE yourProcedure AS
EXEC master..xp_cmdshell 'bcp "SELECT * FROM yourDB.dbo.yourTable" queryout "c:text.txt" -c -T'
GO</pre>
--------------------
To see all answers submitted to the Answer Wiki: View Answer History.
Thank you Carlos. Are there any other options? This option is disabled at my company. Is there an option to do this another way that might work?