Export text file from SQL SERVER 2005

----First Enable xp_cmdshell byusing below command


EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

----now write export query

EXEC xp_cmdshell 'bcp "select * from erp.dbo.tbl_Empleave" queryout "C:\bcptest2.txt" -T -c -t  '

The parameters that were used are:

• The queryout option allows you to specify a query to export. This could be as simple as the query we have are as complicated as you want.  You can also create a view and select the data from a view.
• The file name where the results will be stored is placed after the queryout option.
• The -T parameter specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. You could use -P (password) and -U (user) if you want to use a SQL Server login.
• The -c specifies the character dataype will be used for each field.
• The -t parameter allows you to specify a field delimiter. The character after -t will be used to separate the data fields. If -t is removed, tab will be used as the default delimiter.