10

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) ?

kdbanman
  • 10,161
  • 10
  • 46
  • 78
Brian
  • 1,337
  • 5
  • 17
  • 34

2 Answers2

19

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

Read more at this MSDN article.

kdbanman
  • 10,161
  • 10
  • 46
  • 78
cjohnsson
  • 307
  • 3
  • 8
  • I am sure there was something quirky about what I actually wanted, because at that time I did try -W and it didn't help. Either I did something wrong or forgot what I needed exactly, but your answer does solve what I asked in the post. Thanks. – Brian Sep 03 '13 at 16:07
  • Thera are a way to indent with `-W`? The columns leave only one space separating each other. – Zano Oct 18 '21 at 11:54
  • And looks like not even the `[-s colseparator]` accept tab `\t`. – Zano Oct 18 '21 at 12:00
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.

Dmitry
  • 99
  • 6
  • Thera are a way to indent with `-W`? The columns leave only one space separating each other. And looks like not even the `[-s colseparator]` accept table `\t`. – Zano Oct 18 '21 at 11:55