SQL Server Creating Problematic CSV files

When using the SQL Server (2005, 2008) "Save Results As..." option, I do get a CSV, but SQL Server isn't doing what I would consider proper CSV creation.

Specifically, if my data contains commas, those fields aren't being wrapped in double quotes. The specific data set that I'm looking at right now doesn't contain fields with double quotes, but if it did, I'm not sure SQL Server would do 开发者_运维技巧those correctly, either.

Every week I have to run a query on a database on a remote server, that generates about 36k rows, and I have to get it back to someone in my office in CSV format. Up to now, I've been copying the data, then pasting it into Excel (via Remote Desktop). But pasting 36k rows from a remote clipboard is taking quite a while. Excel gives all sorts of "Waiting for OLE operation to complete" messageboxes as this happens.

I would prefer to just run the query, save the results as a CSV on the remote server itself, then copy down the file.

Long term I will write a process to do this automatically for me, but I've had this problem with SQL Server for many years now, but the pain level hasn't been high enough for me to find a solution until now.

---

Select menu item Tools > Options and the Options dialog appears. On the Options dialog navigate to Query Results > SQL Server > 'Results to Text'. Select for 'Output format' item 'Custom delimiter'. Specify your delimiter in the 'Custom delimiter' field. Click OK to save.

Open a query window and select menu item Query > 'Results To' and either 'Results to File' or 'Results to Text'. Run your query which will be output with your specified delimiter.

For your automated process though take a look at the SQL Server BCP command line utility for outputting your data from SQL Server. Through the command line switches you have complete control over the delimiter and text qualifier. Using BCP is quite easy and I suggest using it even if other elements of your long term process aren't in place

I have an automated process that calls BCP which uses a Stored Procedure to collect data which is zipped and FTPed to a client on a nightly basis. My process is run in a batch file kicked off by a Scheduled Task on the SQL Server machine.

---

Sorry, I'm not at a windows box right now - are you talking about the query manager app in SQL Management Studio?

The best way to do what you want would be to use the data import/export tool you get with SQL Server (sorry, can't check the name right now).

---

That sounds like a SSMS bug rather than a sql server bug. I think Joe's answer will fix your problem.

If you have Integration Services available to you, i'd implore you to look at that, you can probably knock up a package to do it in no time at all.

---

I've been using an in-house made VBS script to export result-sets as CSV files for quite some time now.

Here is a link: exec_script.zip

Here is a sample usage:

c:>cscript //nologo exec_script.vbs /s:MyServer /d:MyDb /q:"exec MyStoredProc" /r:MyFile.csv /header