December 18, 2007 at 8:08 am
Hi once again. I would like to receive emails when Maintenance Plans have completed. In the maintenance plan window you can add 'Notify Operator Task', drag the green arrow and Bingo (well if it worked!!). I created the Operator (although not many parameters to add to the operator profile, i.e. could not specify exchange server). When I execute the plan I receive error:
[Quote]
Date18/12/2007 15:02:08
LogSQL Agent (Current - 18/12/2007 15:02:00)
Message
[264] An attempt was made to send an email when no email session has been established
[/Quote]
I have also configured Database Mail. Test message was sent and received OK. (set profile to public, anonymous authentication)
What am I doing wrong regards setting up an Operator to receive email? What steps should I take?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 18, 2007 at 8:11 am
Maintenance Plans are run by SQL Server Agent. Have you set up the Mail Profile for SQL Server Agent? SQL Server Agent Properties -> Alert System
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
December 18, 2007 at 8:18 am
Jack is correct. There are two mail systems, SQL Mail, run by the SQL Server database engine, and SQL Agent Mail, run by SQL Agent. You need Database Mail set up with a profile for the SQL Agent to send alerts/ notifications.
December 18, 2007 at 8:24 am
Thanks guys. I have now configured 'SQL Server Agent'. Do I need to define a value for 'Alias Local Host Server'. Reason, I have no value and notification still failing!! 🙂
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 18, 2007 at 8:43 am
No you do not need a setting in alias local host server.
If you right-click on Database mail and select View Database Mail Log you will see what is happening and then you can post the error message you are getting when the mail is being sent. You can also run msdb.dbo.sysmail_help_status_sp to see if Database Mail is started and query msdb.dbo.sysmail_event_log. If database is not started you can run msdb.dbo.sysmail_start_sp to start it.
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
December 18, 2007 at 9:58 am
Hi. Here you go:
[Quote]
Date,Source,Severity,Log ID,Message,Process ID,Mail Item ID,Account ID,Last Modified,Last Modified By,Plan Name,SubPlan Name,Task Name,Duration
12/18/2007 15:36:28,,Unknown,,,,,,,,Clean Up Maintenance Plan History,Subplan_1,,00:00:00
12/18/2007 15:36:28,,Unknown,,,,,,,,,,Clean Up History (SQL-MASTER),00:00:00
12/18/2007 15:35:49,,Information,4,DatabaseMail process is shutting down,4496,,,18/12/2007 15:35:49,FERNAU\SQLSvc,,,,
12/18/2007 15:25:49,,Information,3,DatabaseMail process is started,4496,,,18/12/2007 15:25:49,FERNAU\SQLSvc,,,,
12/18/2007 15:18:08,,Unknown,,,,,,,,Clean Up Maintenance Plan History,Subplan_1,,00:00:00
12/18/2007 15:18:08,,Unknown,,,,,,,,,,Clean Up History (SQL-MASTER),00:00:00
12/18/2007 15:14:47,,Error,,,,,,,,Clean Up Maintenance Plan History,Subplan_1,,00:00:00
12/18/2007 15:14:47,,Unknown,,,,,,,,,,Clean Up History (SQL-MASTER),00:00:00
12/18/2007 15:14:47,,Error,,,,,,,,,,Notify Operator Task (SQL-MASTER),00:00:00
12/18/2007 15:11:56,,Error,,,,,,,,Clean Up Maintenance Plan History,Subplan_1,,00:00:00
12/18/2007 15:11:56,,Unknown,,,,,,,,,,Clean Up History (SQL-MASTER),00:00:00
12/18/2007 15:11:56,,Error,,,,,,,,,,Notify Operator Task (SQL-MASTER),00:00:00
12/18/2007 15:03:17,,Information,2,DatabaseMail process is shutting down,3024,,,18/12/2007 15:03:17,FERNAU\SQLSvc,,,,
12/18/2007 14:52:32,,Information,1,DatabaseMail process is started,3024,,,18/12/2007 14:52:32,FERNAU\SQLSvc,,,,
12/18/2007 14:45:17,,Unknown,,,,,,,,Clean Up Maintenance Plan History,Subplan_1,,00:00:00
12/18/2007 14:45:17,,Unknown,,,,,,,,,,Clean Up History (SQL-MASTER),00:00:00
12/18/2007 14:32:59,,Error,,,,,,,,Clean Up Maintenance Plan History,Subplan_1,,00:00:00
12/18/2007 14:32:58,,Unknown,,,,,,,,,,Clean Up History (SQL-MASTER),00:00:00
12/18/2007 14:32:58,,Error,,,,,,,,,,Notify Operator Task (SQL-MASTER),00:00:01
12/18/2007 14:29:28,,Unknown,,,,,,,,Clean Up Maintenance Plan History,Subplan_1,,00:00:00
12/18/2007 14:29:28,,Unknown,,,,,,,,,,Clean Up History (SQL-MASTER),00:00:00
12/18/2007 14:28:27,,Unknown,,,,,,,,Clean Up Maintenance Plan History,Subplan_1,,00:00:00
12/18/2007 14:28:27,,Unknown,,,,,,,,,,Clean Up History (SQL-MASTER),00:00:00
[/Quote]
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 18, 2007 at 10:16 am
I don't see any errors for Database Mail in the log you have posted. Here is an example of output from msdb.dbo.sysmail_event_log with an error:
1 information 9/17/2007 9:59:45 AM DatabaseMail process is started 3596 9/17/2007 9:59:45 AM NT AUTHORITY\SYSTEM
2 error 9/17/2007 10:01:27 AM The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-09-17T10:01:26). Exception Message: Cannot send mails to mail server. (The operation has timed out.).
) 3596 1 9/17/2007 10:01:27 AM sa
3 error 9/17/2007 10:02:27 AM
1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: Could not retrieve item from the queue. Reason: the list of accounts which could send this mail is empty (proba 3596 9/17/2007 10:02:27 AM NT AUTHORITY\SYSTEM
4 information 9/17/2007 10:12:27 AM DatabaseMail process is shutting down 3596 9/17/2007 10:12:27 AM NT AUTHORITY\SYSTEM
5 information 12/5/2007 4:24:03 PM DatabaseMail process is started 3736 12/5/2007 4:24:03 PM NT AUTHORITY\SYSTEM
Do you have any errors? Can you create a simple SQL Agent Job and have it send a notification on completion to the operator?
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
December 18, 2007 at 12:16 pm
Make sure you have restarted SQL Agent after configuring the mail profile for it.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
December 18, 2007 at 1:35 pm
Hi Guys. The 'Restart' did the trick 🙂
So I now have a single Database Mail Profile + a single Operator. The email notification is quite basic but it tells me it completed!
Can anyone advise best practice? I simply want to configure SQL to email events / notifications etc...
Would I ever need multiple Database Mail profiles?
Any good articles you can point me towards and I will have a good read rather than pester you guys!!
Many Thanks to all those that posted.
Phil.
Update:
http://www.sqlservercentral.com/articles/Administration/introtodatabasemailinsql2005/2197/
Amazing what you can find when you simply search 🙂
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 18, 2007 at 4:04 pm
Depends if you need to notify multiple people or not. You could setup a shared mailbox & send emails to that. Or you could set up a distribution list with your admin people in it (or those who need to know) and send email to that.
As the only SQL DBA here, I just have a profile setup for me. I have setup my alerts so that they come from 'SQL-[servername]'. When I am away, I just forward any email from 'SQL-%' to whoever is keeping an eye on things.
You may want different email profiles for different people/jobs. For example, if you have a multi-purpose server. A data import job may fail that one person needs to know about but not necessarily another. It all depends on your configuration, security and needs.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
December 19, 2007 at 3:17 am
Thanks Scott. I am the only DBA here (well plan to be with another course or 3 under my belt plus some real world experiance!!).
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply