Not getting the email notification that I set up
-
I've got a SQL Job that runs once a way, every day at 7:30 AM. It's supposed to send me an email, upon successful completion, however it hasn't since we've migrated from SQL 2005 to SQL 2012. I'm pretty sure that I've got this setup correctly, but it's still not sending me an email upon successful completion. I have myself set up as an operator in SQL, it has the correct email address. So, what am I doing wrong?
Kindest Regards, Rod Connect with me on LinkedIn.
-
Hey,
can you send a successful test email?
could also try Kenneth Fisher's script which has a few different checks for mail 🙂
http://sqlstudies.com/2013/07/29/notes-on-debugging-database-mail-problems/
-
Like the other poster said, what happens when you try sending a test email?
If you try something like this:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMailPublicProfile', -- Profile Name Here
@recipients = 'JoeCustomer@abc.com',
@body = 'TEST EMAIL!',
@subject = 'TEST EMAIL' ;
Does this query run OK, or do you get an error?
-
John Jakob (10/14/2013)
Like the other poster said, what happens when you try sending a test email?If you try something like this:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMailPublicProfile', -- Profile Name Here
@recipients = 'JoeCustomer@abc.com',
@body = 'TEST EMAIL!',
@subject = 'TEST EMAIL' ;
Does this query run OK, or do you get an error?
Hi John,
When I run the SQL script you gave (making appropriate changes, of course, such as using my email address for the recipient) it ends with the following message:
Mail (Id: 1) queued.
And waiting an appropriate amount of time, nothing happens; I never get the email.
Kindest Regards, Rod Connect with me on LinkedIn.
-
Sometimes the fix is to simply restart your SQL Server Agent.
-
Check if email address is right or not ? and another thing profile has been configured correctly ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
You can check the status of your queued emails in the MSDB table sysmail_sentitems. You can also look for errors in the MSDB table sysmail_event_log.
Here's a query that combines the two:
SELECT
log_id AS ID,
event_type as status,
log_date as sent_date,
description,
last_mod_user
FROM msdb.dbo.sysmail_event_log
WHERE event_type = 'error'
union all
SELECT
mailitem_id AS ID,
sent_status as status,
send_request_date AS sent_Date,
'' AS Description,
last_mod_user
FROM msdb.dbo.sysmail_sentitems
ORDER BY Sent_Date DESC
If everything looks ok there, then I'd check to see if there's a firewall or virus scanner that is blocking your server from contacting the SMTP server.
-
Thank you, that was very useful. I've run the SELECT statement that you gave me in msdb and here's the results:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2013-10-15T07:43:29). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not a sa
As you can see, I am getting an error there. I just took a quick look at one of the jobs that runs, and it is running under the sa account, but this error is likely from another job. I've just checked all of the SQL Jobs' histories, but none of them show they were running at 7:43 this morning. I'm a little stumped as to what's going on.
ADDENDUM: Never mind, I know why I saw 7:43 this morning; that's when I ran the test send message. That explains why I don't see any SQL Job that ran at that time.
Kindest Regards, Rod Connect with me on LinkedIn.
-
Bhuvnesh (10/15/2013)
Check if email address is right or not ? and another thing profile has been configured correctly ?As far as I can determine, the profile has been set correctly. I'm pretty sure that the test email I sent did use my email address, but thank you for asking.
Kindest Regards, Rod Connect with me on LinkedIn.
-
Sean Pearce (10/16/2013)
The mail server is not authenticating the sender (your SQL Server). Are you using an exchange server?I believe the answer to your question is yes. We have Office 365, at least for email, and I think that involves Exchange.
Kindest Regards, Rod Connect with me on LinkedIn.
-
We went to Office 365 months ago, and it worked fine then. The only thing that's different now is having migrated to SQL 2012.
Kindest Regards, Rod Connect with me on LinkedIn.
-
We use office365 and our sys engineer setup a relay for us to use with SQL.
Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
-
tim.hidalgo 98121 (10/16/2013)
We use office365 and our sys engineer setup a relay for us to use with SQL.Oh, OK, then I'd better get a hold of my sys admin and see what they can do for us. Thank you!
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply