This might not help much... I believe OSQL is putting a space in there
to pad out the rest of the varchar field. The only time I've had to
deal with this issue it was with a very small data set, so this solution
might not be scalable enough for you.

I used dynamic sql to build a list of queries that pulled the data from
the table one row at a time. The dynamic part was necessary to cast the
varchar field as a char with its exact length.

The idea of it was this:


declare @string varchar( 100), @Command varchar( 1000)
select @string = 'abcdef'

select @Command = 'select ''<tag>'' + convert( char( ' + convert(
varchar( 5), ( len( @string))) + '), ''' + @string + ''') + ''</tag>'''
exec( @command)

What this leaves you with is a query that returns your varchar field
with a precise length, and nothing for OSQL to fill in.


-----Original Message-----
From: Walter Dorciak
[mailto:mssqldba-ezmlmshield-x56516281.[Email address protected]
Sent: Tuesday, May 29, 2007 11:21 AM
To: LazyDBA Discussion
Subject: Osql output - trim characters.

Hi all,

I am creating a simple XML file using osql command (SQL 2000). Osql
calls stored procedure that SELECTs output.

Output is a combination of strings, fields and variables. The problem is
the ouptut includes full length of a variable or field, I am unable to
trim padding spaces.

For example.

Declare @text varchar(100)

Set @text = 'abc'

Select '<element>'
,ltrim(rtrim(@text))
,'</element>'

Even though I try to trim @text, it will be padded with 97 spaces. Is
there an osql switch or any other way that would eliminate trailing
spaces?


Thanks,
___________________

Walter Dorciak
MIS Manager
AGC Electronic Materials
www.agcem.com



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



CONFIDENTIAL AND PROPRIETARY
The contents of this communication are confidential and proprietary to ShopLocal, LLC and may not be reproduced, published or disclosed to others without express authorization of ShopLocal, LLC

Copyright © 2007 ShopLocal, LLC May not be used, copied or distributed without permission. All Rights Reserved.


MS Sql Server LazyDBA home page