Ever needed to save the result of your SELECT query to a text file.
Well use xp_cmdshell. xp_cmdshell is an extended stored procedure kept
in the master database. It issues OS commands directly to the Windows
command shell. You need to be a member of the sys_admin group to use
this command or have the xp_sqlagent_proxy_account.
To save your SELECT query results to a text file, use this query :
EXEC master..xp_cmdshell'bcp "SELECT TOP 5 CUSTOMERID FROM Northwind.dbo.Customers" queryout "c:"text.txt" -c -T -x'
One
word of caution. xp_cmdshell operates synchronously. So the caller has
to wait for the control to be returned to him/her until the
command-shell command is completed.
Note: By default,
xp_cmdshell is disabled in SQL 2005 for security reasons. To enable it,
use the Surface Area Configuration tool or sp_configure. To enable
xp_cmdshell using sp_configure, use this query :
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
