February 24, 2003 at 1:27 pm
Hello all
I have read some recommendations in other topics re dumping data to a text file using xp_cmdshell & osql.
I'm currently doing this to dump out the contents of a varchar column and have come across some formatting problems that I can't figure out. Here's the TSQL command:
exec master..xp_cmdshell 'osql -n -Usa -P**** -q"set nocount on select rtrim(filetext) from _exppsddata where importtableid = 64" -dbranchstage -s'''' -h-1 > c:\dayhsub.psd'
What I'd like to have happen is that each row in table _exppsddata creates a single line in the text file. The outputted (?) column filetext is declared as a varchar(8000). When the file gets created, each line ends up being 8000 chars long & padded with spaces. I would think that a varchar column wouldn't behave thusly. If I use the -w parameter to try & limit the output column length, all it does is embed CR/LF every -w characters & preserves the padding.
The only way I can seem to limit the output length is to change the table's column definition to varchar(max(length of expected data)) before exporting, which I can actually do on the fly but would rather avoid.
This is on a SQL2K SP3 server. Has anyone encountered similar behaviour, am I just missing a command line switch, or am I, to put it politely, hooked?
Any input is appreciated.
Vik
February 25, 2003 at 3:42 am
It is the nature of osql as with isql and QA. The output columns are fixed to the column size, i.e. if you use convert(varchar(20),filetext) then the column width would only be 20.
have you tried bcp?
Far away is close at hand in the images of elsewhere.
Anon.
February 25, 2003 at 7:07 am
No, I completely blanked on bcp. And, on initial consideration, it would work better for me.
Thanks for the help, I'll give it a go.
Vik
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply