SQL Server Results to File Creates extra lines at end

2

When I route the output of my queries to a file, there are one or two blank lines at the bottom. When I run the query though SSMS (2008 or 2012), I get two extra blank lines. When I run the query through SQLCMD, I get 1 extra blank line. How do I get rid of them?

Example (using SSMS):

Set NoCount on
Select '1' as 'One'

The output of this query in the file is:

One
1
(blank line)
(blank line)

or hex 4F 6E 65 0D 0A 31 0D 0A 0D 0A

The extra line(s) at the bottom are causing problems with steps that use the output as input.

Is there a parameter I can Set at the beginning of the query?

Is it a database option?

B540Glenn

Posted 2015-11-18T14:06:27.197

Reputation: 1 045

I'm not sure there is a way to disable the extra blank lines from the SSMS end of things (I've found "results to file" options/control to be very limited in my experiences). Perhaps use a batch that uses SQLCMD to dump to the file, and then use a variation of findstr "." input.txt > output.txt to strip all blank lines from it before passing to the next step? You may have also have to go further and find a way to remove the line-feed off the last line of text. – Ƭᴇcʜιᴇ007 – 2015-11-18T15:03:38.163

I'd like to avoid any extra manipulation of the files because some of them are 1+ GB and growing. But if I have to, findstr better than editing it manually. Thanks – B540Glenn – 2015-11-19T14:25:40.423

Answers

0

Before writing into the file add one conditional split step. Double click the component and in the output name, give the name 'No Empty Row' and in the condition write LEN(TRIM(One)) > 0. Here One is column name this condition will remove those rows having nulls. Now link the output of conditional output to data file destination. Remember to choose No Empty Row. From this you can remove extra rows.

Bharat Prasad Satyal

Posted 2015-11-18T14:06:27.197

Reputation: 1

I think this would work if I were running through SSIS. Unfortunately, SSIS is not an option for this process. – B540Glenn – 2017-08-06T19:42:19.197

Asked: 2015-11-18T14:06:27.197

Viewed: 2 199 times

Active: 2019-12-03T14:01:30.200