Post reply

xp_sendmail adding trailing spaces

  • February 26, 2009 at 7:21 am

    #200292

    SQL 2000

    I have a SQL Server Agent job running the following query

    SELECT Policy_number AS Pol

    UNION

    SELECT UPPER(Policy) AS Pol FROM Table1

    where Table1.Policy is a varchar(25). I want to use xp_sendmail to mail the query in the format:

    Policy_number

    FX1111

    FX21134as

    etc., but the file that is received has trailing spaces padding each line to 50 characters which is causing problems for the recipient. The xp_sendmail options I'm using are:

    @subject='Policies',@attach_results='TRUE',@attachments='policy.csv',@no_header='TRUE',@separator='',

    @message='New policies'

    I'm using @separator='' to prevent a leading space appearing in the data and @no_header='TRUE' to eliminate the line of dashes that appears under the column header. I can't use @width as the policy numbers are of varying length. How do I get round this?

    Thanks

    Scott

    --
    Scott

  • February 26, 2009 at 8:38 am

    #950637

    Based on what I read in BOL, your only option for this is to use the @width parameter which you seem to think you can't use. HAve you tried setting @width to 25 since the max size of the Policy column is 25. The default setting for @width is 80.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 26, 2009 at 9:01 am

    #950653

    Hi Jack

    Setting @width=25 just pads each line out to 25 characters, which isn't the desired effect.

    --
    Scott

  • February 26, 2009 at 9:17 am

    #950670

    But it reduces the the added characters from 50 to 25, which I would consider an improvement.

    Have you tried doing an RTRIM in the query?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 26, 2009 at 9:49 am

    #950694

    RTRIM has no effect. I suppose fewer spaces is an improvement, but the client won't be happy until there are none. It's good, but it's not right.

    --
    Scott

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply