I had a task to output or generate CSV file using SQL Sever management Studio.

After some searching and learning, I got the following steps to get my tasks done:

1: Open SQL Server Management Studio 2005.
   Tools > Options > Query Results > SQL Server > General > Default destination for results;

      Set to “Results to Text”

  

2: Tools > Options > Query Results > SQL Server > Results to Text > Select: comma delimited

After the above 2 steps setting. then when you input any “Select … From …” script in your SQL Management Studio, you will see the result is in CSV format.