February 16, 2005 at 7:27 am
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
February 16, 2005 at 7:47 am
Have you tried inserting a column-separator for the @Separator value?
February 16, 2005 at 7:49 am
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.
February 16, 2005 at 7:53 am
Your @Separator is set for a blank space. Can you set it to read something else? like a . or ,
February 16, 2005 at 8:04 am
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!!!
February 16, 2005 at 8:12 am
Have you tried to shorten the space between your ' ' ?
It may seem weird but sometimes the extra tab sends the flow off.
February 16, 2005 at 9:15 am
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.
February 16, 2005 at 9:50 am
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
February 16, 2005 at 9:55 am
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.
February 16, 2005 at 11:14 am
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
February 17, 2005 at 2:12 am
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...
February 17, 2005 at 1:33 pm
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