August 12, 2004 at 2:00 pm
I am using the xp_cmdshell to run a sql select statement and direct the output to a file. Everything works fine except the file has a blank at the beginning of each line. Is there some way to prevent this from happening? Also is there a way to pad the end of the line with blanks? I tried replicate in the sql and including a column with blanks but both of these methods truncated the blanks.
August 13, 2004 at 8:10 am
I use xp_cmdshell to do what you describe and I don't get a blank line. Post your script so I can compare.
As far as padding blanks, convert your last row to char() and it will pad. For example, convert(char(50),'Hello World') will pad 39 blanks at the end.
August 23, 2004 at 11:49 am
Thanks for the repy. However, I don't have a problem with a blank LINE. The problem is each line has a space (blank) at its beginning. Code appears below (col1 col2 col3 are defined as char(#) and always have the exact number of chars in them equal to the definition)
Set @sql = 'SELECT col1 + col2 + col3 from tmp_table
Set @cmd = 'isql -U usrname - P pswrd -S Servername -d database -h-1 -Q"' + @sql + '" -o D:\filename
EXEC master..xp_cmdshell @cmd
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply