Here I am explaining how to export query results into text file. Please follow below steps to archive this thing.
Step 1:
Enabled the xp_cmdshell
By default 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 below query :
EXEC master.dbo.sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
RECONFIGURE
Step2:
To save your SELECT query results to a text file, use below query :
EXEC master..xp_cmdshell ‘bcp “select * from adtype.dbo.adbrite” queryout “c:\text.txt” -c -T -x’
or
EXEC master..xp_cmdshell ‘bcp “select * from Adbrite..Adtype” queryout “c:\text.txt” -U sa -P sa123-c -T -x’
Database Name : Adbrite
Table Name : Adtype
Database User Name : sa
Database Password : password
for more detail : http://amitpatriwala.wordpress.com/2010/04/02/save-select-query-result-into-textfile/
