CODEWITHSUNDEEP
sql-serversql-server-2005sqlcmd
Brian
Brian

Reputation: 1397

Remove trailing spaces from fixed length in output file generated by sqlcmd

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

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

Upvotes: 10

Views: 17849

Answers (2)

Dmitry
Dmitry

Reputation: 129

-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.

Upvotes: 5

cjohnsson
cjohnsson

Reputation: 307

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

Read more at this MSDN article.

Upvotes: 19

Related Questions