May 2, 2008 at 12:41 pm
What is the method (if there is one) to notify multiple operators of a single job’s run conditions...ie
Failures, success, or completions? The present database mail configuration works great for a single
operator to receive a single job status… success or fail or complete. However, if I want to assign
multiple operators of the same job’s status, the operator window drop down menu of the job properties dialog box limits me to assign one operator at a time. Thanks to everyone for being there to help.
SQL 05 SP2
May 2, 2008 at 1:19 pm
I tend to create groups in Exchange (assuming you are using Exchange). Then it is only a single email address to send a message to everyone.
May 4, 2008 at 8:45 am
Thanks Earl. I use groups to send a job status. Problem is SQL limits sending a status to one person or group at a time…. a Success or failure or completion. If I need to send a success,or failure or completion to more than one person or group for the same job it’s not available.
May 4, 2008 at 9:15 pm
You could always have the notification as a job step (one for success, one for failure). Then use sp_send_dbmail.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 5, 2008 at 6:50 am
Here is another solution that might work for you.
Assign an Operator multiple email addresses and use that in the job.
OR use this in a procedure.
USE msdb ;
GO
EXEC dbo.sp_notify_operator
@profile_name = N'Default',
@name = N'Test',
@subject = N'Test Notification',
@body = N'This is a test of notification via e-mail.' ;
GO
Using Exchange groups is a good solution depending upon your relationship with the people that administer Exchange. We have outsourced Exchange so it is a lot of bureaucracy to change anything.
May 19, 2008 at 7:59 pm
As webooth says, you can assign multiple email addresses to an operator. I think that's an undocumented "feature" and I use it.
On my servers, I usually create 2 operators: DBA which has only my address, TechSupportAndDBA which has my address and the address of the Help Desk group. Jobs in production notify TechSupportAndDBA upon completion - whether successful or failure.
In the Operator;'s email address line put Address1;Address2 separated by semicolon.
It's a bit clumsy but it works. I think MS should enable 1 operator to be notified upon failure and a different operator to be notified when the job completes successfully.
John
November 21, 2008 at 8:43 am
This is somethig else that Microsoft has screwed up in SQL 2005. Thanks for nothiing.
January 6, 2009 at 1:47 am
Experts,
I am having requirement to send mail(notify) to list of email id stored as 2 columns in a SQL table. I am having a plan of writting cursor to retreive email id from the table and using database mail to send mail.
Please confirm me the possibility of my solution or provide me any other possble solution.
Is there a restriction to send mail to multiple email ids using database mail.
January 6, 2009 at 3:01 am
In SQL Server Agent Create Operators and in Notification options-Email nme: write the e-mail address by seperating ";"
e.g. email1@email.com; email2@email.com etc.
Simply Assign this operator to notify.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
January 6, 2009 at 3:17 am
Ed Wyant (5/2/2008)
What is the method (if there is one) to notify multiple operators of a single job’s run conditions...ieFailures, success, or completions? The present database mail configuration works great for a single
operator to receive a single job status… success or fail or complete. However, if I want to assign
multiple operators of the same job’s status, the operator window drop down menu of the job properties dialog box limits me to assign one operator at a time. Thanks to everyone for being there to help.
SQL 05 SP2
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'test1@mail.com,test@mail.com,... ',
@body= 'ur body text,
@subject ='mail',
@profile_name = 'ur profile'
January 6, 2009 at 4:14 am
Hi All,
Thanks for your technical information..
Cheers,
January 11, 2009 at 3:00 am
I want to pass value for mutiple recipients without mentioning the mailid of the recipient as follows,
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test',
@recipients = @empemailid, @manageremailid, @body = @body,
@subject = @subject
or
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test',
@recipients = "@empemailid, @manageremailid", @body = @body,
@subject = @subject
or
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test',
@recipients = ["@empemailid", "@manageremailid"], @body = @body,
@subject = @subject
but this is not working.
Please inform me that is this possible or enlighten me for correct way of passing the value through variable.
BR,
Parthi
September 14, 2009 at 2:23 pm
Parthipan,
Try this. It should work.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test',
@recipients = [@empemailid ; @manageremailid],
@body = '@body',
@subject = '@subject'
SQL DBA.
November 1, 2009 at 7:28 pm
There are three options that I've verified to work up through SQL2008, all each being variants of the sp_notify_operator SSP:
1) Add an operator to the group, like "DBA_Group" and in the Email Name textbox, specify the email addresses separated by semi-colons, like: john@fictionalcompany.com;bob@fictionalcompany.com
2) Add a "Notify Operator Task" maintenance plan task to the job (assuming you created it as a maintenance plan). In the Notify Operator Task window, you will be able to check as many operators as you wish in the "Operators to notify" section. If you click on the "View T-SQL" button below, you'll note that what SSMS is doing is adding three separate and distinct "execute sp_notify_operator" calls to the system.
3). Similar to #2, if you haven't created your job under the Maintenance Plans subsystem, you can achieve the same effect by adding an additional Step to your job. Specify in the prior step that, upon failure (or whatever your logic is), the routine should go to your final step, which executes a T-SQL statement like follows that includes all of your operators:
EXECUTE msdb.dbo.sp_notify_operator @name=N'John John',@subject=N'Backup failure on YourServerName',@body=N'The backup for the YourDatabaseName database failed. '
EXECUTE msdb.dbo.sp_notify_operator @name=N'Bobby Bob',@subject=N'Backup failure on YourServerName',@body=N'The backup for the YourDatabaseName database failed. '
Make sure that "John John" and "Bobby Bob" have their own Operator profiles created under the SQL Server Agent.
November 2, 2009 at 8:34 am
Another method I have used is to set up a rule in Outlook forwarding the database mail to the relevant people. You can create any number of rules for each server. It is also quick and easy to change the recipients
Madame Artois
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply