Blank lines in a .csv files created through SSMS :OUT command

Question

I'm using code like this in MSSQL 2008 R2 management Studio to write the results of a SELECT statement to a csv file on a network share:

SET NOCOUNT ON;   
GO    
:OUT \\163.123.45.678\SomeFolder\mydata.csv    
SELECT id, name, surname FROM sometable;    
GO

This creates the mydata.csv file at the correct location but there is an extra blank line at the end of the csv file. How do I prevent that blank line from being created in the csv file?

Is the above the best way to write the output of a sql query to a text file? I can't use BCP.

Thanks.

Answer 1

I think you can use SSIS package to get the query result in .CSV format. sometime you may get blank line or space in between two lisne, in that case 'Derived Column' transformation task and remove or trim the unwanted spaces and lines.