Sqlcmd trailing spaces in output file

Question

Here is my simplified scenario:

I have a table in SQL Server 2005 with single column of type varchar(500). Data in the column is always 350 characters in length.

When I run a select on it in SSMS query editor, copy & paste the result set in to a text file, the line length in the file is 350, which matches the actual data length.

But when I use sqlcmd with the -o parameter, the resulting file has line length 500, which matches the max length of varchar(500).

So question is, without using any string functions in select, is there a way to let sqlcmd know not to treat it like char(500) ?

Answer 1

You can use the sqlcmd formatting option -W to remove trailing spaces from the output file.

Read more at this MSDN article.

Answer 2

-W only works with default size of 256 for variable size columns. If you want more than that you got to use -y modifier which will tell you its mutually exclusive with -W. Basically you are out of luck and as in my case file grows from 0.5M to 172M. You have to use other ways to strip white space post file generation. Some PowerShell command or something.