April 16, 2007 at 1:43 pm
I am looking for a way to utilize xp_sendmail to send a formatted email to an operator on the system. Has anyone done this? The core reasoning for this configuration option is that we have several processes which run via SQL Agent which send success notifications. The success notifications are sent via completion of the job and we are moving to the use of xp_sendmail in these processes to allow us to give more descriptive status communication to the intended audience. Here is where the fun comes in. This audience changes regularly between different exchange distribution lists and we would like to keep a single administration point for all processes to avoid confusion when the audience changes.
Thanks everyone!
April 17, 2007 at 2:49 am
If you are thinking of using the job completion notification functionality in Agent, then you are stuck with the standard mail format used by Agent.
The best you can do is to utilise the Pager functionality... It is called pager, but the destination can be a standard e-mail address. If you specify a message is to be sent to Operator X via pager, you can define custom prefix and suffix data for the message subject. We use this for critical messages - they all get sent to a 'DBA Callout' operator and get an eye-catcher added to the subject in a (desperate 🙂 ) attempt to get us DBAs to deal with them quickly.
If you have a dedicated xp_sendmail step in your job, or your application calls xp_sendmail direct, then you have complete freedom to define your e-mail format. We use this for some applications to feed problems directly into our Remedy problem-tracking system.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 17, 2007 at 2:57 pm
EdVassie,
Thanks for the reply. What I am looking for is to truly integrate the Operator with the xp_sendmail's @Recipient value. Meaning something similiar to teh below that works:
use master
go
exec xp_sendmail @Recipient=[SQL Operator name],@Subject='Test',@Message='Testing xp_sendmail with Operator'
This makes it so that I don't have to manage the email addresses after establishing the code for the xp_sendmail call. Instead I would only have to change the email address associated with the operator to effectively propogate this change to all xp_sendmail calls for that server.
April 19, 2007 at 10:06 am
As far as I know, @recipient must refer to a e-mail address, not to a SQL operator.
If you want to put a layer of indirection into the process, you can get distribution lists set up in Exchange, and use these as the subject of @recipient. In this way you can change the list of people in the distribution list at any time, without needing any change to your SQL application.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 19, 2007 at 10:26 am
EdVassie,
That is what I was afraid of. Thanks for the help.
November 6, 2008 at 4:59 pm
I was wondering if you had any secrets to that distribution group. I just moved the mailbox associated with the SQL Service account to exchange 2007 and now xp_sendmail doesn't work. I get no error message that can be found, it said it send it, but it doesn't.
November 6, 2008 at 5:02 pm
I'm getting tired, let me clarify that post. I can send mail from Agent to an operator; I can send via xp_sendmail to a list of email accounts, but when I use a distribution group, it acts almost like it's just text. If I send to a receipent and a group, the recipient gets the mail, with the group name in the TO list, but the group doesn't get the mail.
December 16, 2009 at 6:12 pm
I've got same problem too. After our Server Engineering move the mailbox to Exchange 2007. I'm working with SE and We can see the report that all messages have been sent to the group but the member of the group didn't get it.
December 17, 2009 at 1:18 pm
This obviously sounds more like an Exchange issue than an issue with xp_sendmail. Did any of the properties of the distro group change? Can members of the distro group receive e-mail from a user using Outlook or OWA? If so, I would then test to see if a user in the distro group can receive an e-mail from someone sending a test via telnet on port 25 using SMTP commands. What have your Exchange admins said on the issue?
Joie Andrew
"Since 1982"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply