No doubt that a DTS or SSIS package can be used to export SQL Server table data to a text file. The below article is to demonstrate another way to export SQL Server table data to a text file (This is with reference to SQL Server 2005 and above).

The xp_cmdshell extended stored procedure runs operating system commands from within the database engine. This is disabled by default in SQL Server 2005. This can be enabled in two ways:

1. Use sp_config to update advance options

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

• Open SQL Server 2005 Surface Area Configuration Tool
• Expand SQL 2005 >> Database Engine
• Choose xp_cmdshell
• Check the checkbox “Enable xp_cmdshell”
• Apply the changes in the setting

EXEC xp_cmdshell 'bcp "SELECT * FROM [MyTableName]" queryout "D:\MyTableName.txt" -T -c -t,'

queryout >> allows you to specify a query to export

File Name >> where the results will be stored and is placed after the queryout option

-T >> specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security (To connect through SQL Server login use –P for password and –U for user name)

-c >> specifies the character data type will be used for each field

-t >> 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