Save Sql Recordset into the csv file with well formatted?

Question

I need to dump my sql query result into the text file. i have created the following query,

DECLARE @cmd VARCHAR(2048) 

SET @cmd = 'OSQL -Slocalhost ' 
            + ' -UCRN370 -PCRN370' 
            + ' -Q"SELECT TOP 5 GageId FROM EwQMS370..msgages"' 
            + ' -oc:\authors.csv' 

EXEC master..xp_cmdshell @cmd, NO_OUTPUT

The above query created the CSV file authors.CSV. But the content of the file is not formatted properly. They showed some junk data.

I need to create a formatted csv file.

Answer 1

You could do this with BCP, CLR, SSIS or DTS given your example BCP is likely the best fit.

BCP EwQMS370..msgages out c:\Authors.csv -c -t, -T –S<servername>

You can still run this through xp_cmdshell in the same way as you are in your example; only BCP will do the heavy lifting of the CSV formatting for you. Booksonline will go through BCP in more detail. If you need to select less columns from your table create a view to restrict the data down.