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