September 29, 2011 at 12:33 pm
This has been a multiple-day effort to troubleshoot this, so I'll try to be as concise as possible in the hopes that someone has a suggestion that will get this working for me.
Rightclicking on 'Database Mail' and selecting 'send test email' works.
Putting the following SQL code in a job step works when the job executes on schedule:
USE msdb
GO
EXEC sp_send_dbmail @profile_name='<the profile name>',
@recipients='mrtexasfreedom@fema.gov',
@subject='SQL MAIL HARDCODED test',
@body='This is email sent from within the step....'
The thing that never works is for the notification to send out an email when the job completes. The management logs for Database Mail show "Database Mail process is started" and also "stopping". But no failure or error.
These queries show the entries for when I have the above SQL embedded in the job, but don't display any mention of the notification email being attempted.
SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO
In the notifications section for the job, my operator is selected with a check in the email box and I've tried 'when job completes' or "when the job succeeds."
Per another suggestion somewhere, I bounced the SQL Server Agent process as well.
Anyone have any experience with this (un)functionality of SQL Server 2008 R2? If I can't get this to work, I'm going to probably put a final step in the job that sends the email and configure the second to last step to quit the job on success or go to next step on failure. It's kind of a kludge, but I can't afford spending much more time on this issue.
thanks in advance for any suggestions,
mtf
September 29, 2011 at 1:28 pm
Might check that the "enabled" checkbox is checked in the operator properties, and that you don't have a pager on-duty schedule set up that is preventing the e-mail from sending.
The Redneck DBA
September 29, 2011 at 1:36 pm
You also have to enable the profile on the SQL Agent properties, Alert System screen and restart the service.
September 29, 2011 at 1:42 pm
Jason,
Thanks for the reply. I just now checked the operator (myself) and the enabled check is in place. No pager duty schedule settings have been made. I checked the 'notifications' section of the operator and clicked on the 'jobs' radio button. It lists the step that is supposed to send the email on success. Meanwhile, history confesses that no email has ever been sent to the operator.
I know the job is succeeding because I am verifying the changes it makes in the database on each run.
any other ideas?
mtf
September 29, 2011 at 1:46 pm
Chuck Hottle (9/29/2011)
You also have to enable the profile on the SQL Agent properties, Alert System screen and restart the service.
Wow. What kind of maniacs embedded that hidden pitfall into this system? Talk about spreading out related configuration settings across the countryside....
I just added a check to the 'enabled' box for the profile and will restart the sql server agent shortly. I'll let you folks know how this turns out in about two hours.
Appreciatively,
mtf
September 29, 2011 at 1:51 pm
mrTexasFreedom (9/29/2011)
Chuck Hottle (9/29/2011)
You also have to enable the profile on the SQL Agent properties, Alert System screen and restart the service.Wow. What kind of maniacs embedded that hidden pitfall into this system? Talk about spreading out related configuration settings across the countryside....
I just added a check to the 'enabled' box for the profile and will restart the sql server agent shortly. I'll let you folks know how this turns out in about two hours.
Appreciatively,
mtf
I'm not sure I would consider setting up database mail setting up alerts/notifications for agent jobs to necessarily be related.
It's possible that you would want to only use database mail for sending mail from stored procs or whatever and not want to ever send out notifications about jobs.
You also wouldn't want you agent to make an assumption about which mail profile you want it to use for notifications - I'm sure that's why they don't turn one on by default.
The Redneck DBA
September 30, 2011 at 8:48 am
This issue has been SOLVED.
I checked the SQL Server Logs and found the following error message:
an attempt was made to send an email when no email session has been established sql server 2008
So, then I looked on the Google Answer Machine and found that SQL Server Agent must be restarted through the SQL Server Configuration Manager for the job notifications to be recognized by the SQL Server Agent. Also, it helps to unenable and re-enable the profile in SQL Server Agent > Properties > Alerts prior to the agent restart.
Thanks to everyone for their help on this issue. No I can close out this week feeling like I made some progress.
mtf
November 23, 2011 at 2:47 am
This still didn't work for me. Any more solution?
I m using SQL SERVER 2008 R2.
Result of "Select @@VERSION"
--------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1753.0 (X64) Dec 10 2010 22:13:00
Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)
--------------------------------------------------------------------------------------------
I have 2 DBmail profile. Profile1 and Profile2.....I have set Profile2 as public and default....and i set profile2 as mail profile in Sql server Agent -> Properties -> Alert System.
Every time when i go back to Sql server Agent -> Properties -> Alert System, It shows me Profile1 as selected.....don't know how this is happening.
Test mail is working fine.
EXEC msdb.dbo.sp_send_dbmail is also working fine.
But Sql Agent job notification is not working.....it is not even logging it in msdb.dbo.sysmail_allitems table.
Feeling helpless.....
November 25, 2011 at 1:01 am
Suddenly started running....don't know what happened.;-)
September 27, 2012 at 10:49 pm
ok, so this may be a little late!
I had the problem where Database mail had been configured, I could send a test email but when I created an operator and then tried a notification via email of a success or failure of a job no email was forthcoming.
Anyway follow these instructions to fix the issue.
http://msdn.microsoft.com/en-us/library/ms186358.aspx
Security
Permissions
The user creating the profiles accounts and executing stored procedures should be a member of the sysadmin fixed server role.
Using SQL Server Management Studio
To configure SQL Server Agent to use Database Mail
In Object Explorer, expand a SQL Server instance.
Right-click SQL Server Agent, and then click Properties.
Click Alert System.
Select Enable Mail Profile.
In the Mail system list, select Database Mail.
In the Mail profile list, select a mail profile for Database Mail.
Restart SQL Server Agent.
Follow-up Tasks
The following tasks are necessary to complete the configuration of Agent to send alerts and notifications.
Alerts
Alerts can be configured to notify an operator of a particular database event or operating system condition.
Operators
Operators are aliases for people or groups that can receive electronic notification
http://msdn.microsoft.com/en-us/library/ms186358.aspx
finally
After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO
cheers
Robo
September 25, 2020 at 8:33 pm
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply