January 2, 2007 at 10:51 am
I'm having some trouble setting up SSIS in an environment where I don't have complete control, so I thought I'd accomplish exporting an ASCII file by running the SELECT as a command line job and schedule it with a scheduler. Oh, but if it were only that easy.
After trying iSQL and oSQL, which both introduced extraneous characters and padding, I tried the new CMDSQL which did the cleanest job, but I'm still having a problem with spaces getting added to the right of the result rows. It seems like for every column referenced in the SELECT, more spaces are added. This is true even if I concatenate each column and it's true if I use RTRIM() on each column and it's true even if I RTRIM() the whole concatenated mess. It's true if I turn the SELECT into a virtual table like so:
SELECT * FROM (SELECT RTRIM(COL1 + '|' + COL2 + '|' + COL3) FROM MYTABLE) AS MYVIRTUALTABLE
This is very strange.
My command is:
sqlcmd -Smyserver -Umylogin -Pmypassword -dmydatabse -ic:\PAudit.sql -od:\PAdit.txt -h-1
Does anyone know about this padding? a workaround? an alternative approach?
Thanks.
David
January 2, 2007 at 11:04 am
You might want to try the "-W" option. From BOL:
This option removes trailing spaces from a column. Use this option along with the -s option when preparing data that is to be exported to another application. Cannot be used with the -y or -Y options.
January 2, 2007 at 12:01 pm
You can use W switch as David mentioned but I don't understand why you are using subquery...
SELECT * FROM (SELECT RTRIM(COL1 + '|' + COL2 + '|' + COL3) FROM MYTABLE) AS MYVIRTUALTABLE
If you want to separate the columns with "|" you can use s option as David mentioned...or
you can also use the folloiwng...
SELECT RTRIM(COL1 + '|' + COL2 + '|' + COL3) as Col123 FROM MYTABLE
MohammedU
Microsoft SQL Server MVP
January 2, 2007 at 12:06 pm
David,
Many thanks -- the -W worked perfectly. Somehow I missed it when I did a "SQLCMD /?".
DS
January 2, 2007 at 12:08 pm
Mohammed,
The subquery was just an example of the back flips I was doing to try to "hide" the multiple columns from SQLCMD which seemed to want to tack about 15 extra spaces to the end of the line for each column listed. Obviously, it was silly for me to do that since there is a nice -W to handle this.
Thanks for your post.
David
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply