sqlcmd: reconciling -W and -Y

I have an automated batch process that uses sqlcmd to pull data from a database and dump it into a text file. Many fields in that database are of type varchar(max), Sqlcmd limits these fields to 256 characters unless I add something like -y 0 to the flags in the sqlcmd call.

This gives me the full text for fields larger than 256 characters, but it also adds a great deal of whitespace; the fields are padded to make开发者_StackOverflow each field as big as it could possibly be according to its data type, essentially giving me huge files with lots of padding and wasted space.

I could fix this by adding -W to my sqlcmd flags, but this gives me an error saying that -W and -y are incompatible.

Has anyone had this problem before? Thoughts on how to solve it?

From this thread there is the suggestion that specifying the column separator using -s can trim the data as if it is not specified the data comes out fixed width.

If that does not work have you tried RTRIM(LTRIM(ColumnName)) in your SELECT query?

I had this issue while creating a CSV file with SQLCMD and got it solved by tricking SQLCMD. Instead of returning several fields and let SQLCMD put the comma as separator, I just concatenate all the fields putting the commas myself in it. I know it's an ugly workaround, but that solved my problem. Hope it can help someone else.