Export query result to .csv file in SQL Server 2008

1. Open SQL Server Management Studio
2. Go to Tools > Options > Query Results > SQL Server > Results To Text
3. On the far right, there is a drop down box called Output Format
4. Choose Comma Delimited and click OK

Here's a full screen version of that image, below

This will show your query results as comma-delimited text.

To save the results of a query to a file: Ctrl + Shift + F

I know this is a bit old, but here is a much easier way...

1. Run your query with default settings (puts results in grid format, if your's is not in grid format, see below)

2. Right click on grid results and click "Save Results As" and save it.

If your results are not in grid format, right click where you write the query, hover "Results To" and click "Results To Grid"

Be aware you do NOT capture the column headers!

Good Luck!

You can use PowerShell

$AttachmentPath = "CV File location"
$QueryFmt= "Query"

Invoke-Sqlcmd -ServerInstance Server -Database DBName -Query $QueryFmt | Export-CSV $AttachmentPath

If the database in question is local, the following is probably the most robust way to export a query result to a CSV file (that is, giving you the most control).

1. Copy the query.
2. In Object Explorer right-click on the database in question.
3. Select "Tasks" >> "Export Data..."
4. Configure your datasource, and click "Next".
5. Choose "Flat File" or "Microsoft Excel" as destination.
6. Specify a file path.
7. If working with a flat file, configure as desired. If working with Microsoft Excel, select "Excel 2007" (previous versions have a row limit at 64k)
8. Select "Write a query to specify the data to transfer"
9. Paste query from Step 1.
10. Click next >> review mappings >> click next >> select "run immediately" >> click "finish" twice.

After going through this process exhaustively, I found the following to be the best option

PowerShell Script

$dbname = "**YOUR_DB_NAME_WITHOUT_STARS**"
$AttachmentPath = "c:\\export.csv"
$QueryFmt= @"
**YOUR_QUERY_WITHOUT_STARS**
"@

Invoke-Sqlcmd   -ServerInstance **SERVER_NAME_WITHOUT_STARS** -Database  $dbname -Query $QueryFmt | Export-CSV $AttachmentPath -NoTypeInformation

Run PowerShell as Admin

& "c:\path_to_your_ps1_file.ps1"