MSSQL 2005: Example BCP Query to Text File

Prerequisites:

- MSSQL 2005
- AdventureWorks DW

Steps:

1. Enable xp_cmdshell on the SQL instance
- Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server 2005 Surface Area Configuration
- Surface Area Configuration for Features > MSSQLSERVER > Database Engine > xp_cmdshell
- Enable xp_cmdshell: Check

2. Prepare your query
- Start > Programs > Microsoft SQL Server 2005 Management Studio
- New Query

3. Execute your query using BCP
- Start, Run, CMD
- Change Directory: C:\Program Files\Microsoft SQL Server\90\Tools\Binn
- bcp "select firstname, lastname from dbo.dimcustomer" queryout c:\awdwcustomer.txt -T -C RAW -t ","

4. Review your output
- C:\awdwcustomer.txt

e.g.
Jon,Yang
Eugene,Huang
Ruben,Torres
Christy,Zhu
Elizabeth,Johnson
Julio,Ruiz
Janet,Alvarez
Marco,Mehta