xp_sendmail - Formatting the query output

  • Hi ya all,

    I am totally stuck on this, please help!!!!

    All I am trying to do is send email notifications to the purchasing department a list of current PO status. Now I have this big query (but can be as simple as Select customername, custno, dateregistered From Customers) that I am attaching to xp_sendmail as parameter.

    @messg = char(13)+ 'Here is today's report:'

    EXEC master..xp_Sendmail

    @recipients = 'myname@mydomain.com',

    @no_header = 'true',

    @separator ='  ',

    @query = 'Select customername, custno, dateregistered From Customers WHERE IsPendingEmail =1',

    @attach_results = 'false',

    @message = @messg,

    @subject = 'PO status summary',

    @width=130

    The email has enormous amount of white spaces and looks ugly. I played with the "@width" parameter to control the width of display in email, but no help. Am I missing something very obvious?

    Please help. Thanks.

    EthanHunt

  • Have you tried inserting a column-separator for the @Separator value?

  • Thanks for your reply. YES, I have tried @seperator parameter as well. see below

    EXEC master..xp_Sendmail

    @recipients = 'myname@mydomain.com',

    @no_header = 'true',

    @separator ='  ',

    @query = 'Select customername, custno, dateregistered From Customers WHERE IsPendingEmail =1',

    @attach_results = 'false',

    @message = @messg,

    @subject = 'PO status summary',

    @width=130

    Do I need to specify a different value?

    Thanks.

  • Your @Separator is set for a blank space. Can you set it to read something else? like a . or ,

  • Martin, thank you for your comments.

    I tried changing the @seperator parameter to . and ,

    Each time, those characters were placed at the end of the column record, but it didn't get rid of the white space I get. I also tried trim(). I am getting results like...

    PONumber             Status

    ------------------------------------

    258449                                                  ,accepted                                    

                  ,

    Is there an alternate way to attach query output to email?Help!!!

  • Have you tried to shorten the space between your '  ' ?

    It may seem weird but sometimes the extra tab sends the flow off.

  • I noticed something... the white spaces I am tryign to get rid off are 'tab's. I tried setting @seperator = 'tab' but that just puts 'tab' in the beginning of every column.

     

  • If you want a tab as your separator, would '\t' work? Also, can you RTrim() the whitespace out of your columns to shorten up the display?

    2 cents,

    SJT

  • SJT,

    \t would definitely work. I guess I didn't fully understand what the @seperator parameter does. But now I know it is just to seperate the two columns and that is not my worry.

    I tried rtrim() as well. It is not that the data contains trailing white spaces but the default column size (in Query analyzer the default is 256 and the lowest you can set is 30). My issue is with the column size.

    Is there any better way to email the query output in a desired formatted manner?

    Thanks.

  • You could use @attach_results = 'true' with your @width setting to pretty up the output. I'm not seeing a way to make the results in a simple, truly delimited format. If that's what you need, you might try bcp with a format file and then use xp_sendmail with the @attachments param set to the resulting output file.

    HTH,

    SJT

  • Maybe I'm missing the point, but I don't see what this has to do with QA. Why don't you use "substring" to make the output more readable? Something like: select substring(customername,1,15) from customers

    So your column will be 15 characters width. Sorry if I didn't understand your question...

  • What about something like:

         @query = 'Select trim(customername) + '' '' + custno + '' '' + dateregistered as customer_info From Customers WHERE IsPendingEmail =1',

    You will need to add convert if the custno and dateregistered fields aren't strings.  if you want to get rid of tabs, try wrapping the offending expression in replace, such as:

        trim(replace(customername, char(9), ''))

    Hope this helps.

    So long, and thanks for all the fish,

    Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Viewing 12 posts - 1 through 11 (of 11 total)

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