October 30, 2013 at 8:11 am
I've set up an email profile in Database Mail, and tested it. It seems to work when I run it manually. However, when SQL Jobs run and they send emails, which they should, it doesn't work at all. I've contacted the system admins and they've setup a relay, which I understand has to happen so Database Mail can send emails. But even so, it's still not sending emails.
What's my next step?
Kindest Regards, Rod Connect with me on LinkedIn.
October 30, 2013 at 9:19 am
Have you enabled the Database Mail Profile in the SQL Server Agent properties? This is a step I always missed when I first started out!
October 30, 2013 at 12:45 pm
liteswitch (10/30/2013)
Have you enabled the Database Mail Profile in the SQL Server Agent properties? This is a step I always missed when I first started out!
Ummm, no. (As I hang my head in shame.)
OK, I've just done that. I'll see what happens tomorrow.
Kindest Regards, Rod Connect with me on LinkedIn.
October 30, 2013 at 2:07 pm
You will also need to restart the SQL Agent in order for the new mail settings to take affect.
November 1, 2013 at 8:42 am
Hi JeremyE,
I checked today, but still didn't get any email notification that the job succeeded, which I should be getting. I've enabled the profile, as you suggested, and restarted the SQL Server Agent. I've double checked the SQL job in question and it has been set up to send me an email l upon successful completion. All of the jobs in the past week, including this morning, have run successfully. So what could now be the problem?
Kindest Regards, Rod Connect with me on LinkedIn.
November 1, 2013 at 9:25 am
Is there anything in the SQL Server log itself from when the job ran?
Create a new identical job, but change it to doing a simple T-SQL select and then emailing you on job completion, so you can test it with that process as often as needed, so we can tinker with it without disruption.
November 1, 2013 at 9:41 am
I would echo what liteswitch said and suggest creating a test job with a simple step like SELECT '1' so you can run the job anytime. I would also go back through and double check everything (SQL Agent Alert System setup, operators, job notification) . This BOL article has the setup steps. http://technet.microsoft.com/en-us/library/ms186358.aspx
The most common issue I see is with people forgetting to restart SQL Agent which is why I suggested it earlier.
November 21, 2013 at 9:45 am
I've got a few minutes to look into this, so I took the time. This time I ran another job which I know is supposed to send out an email once certain conditions are met. I know that those conditions will be met, so I ran made the job run in SQL Agent, and it gave an error. Here's the error:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) (Microsoft SQL Server, Error: 121)
Here's the SP that it runs, and which has always worked before:
CREATE PROCEDURE [dbo].[spMonitorMoneyPools_Sub1]
-- Add the parameters for the stored procedure here
@EmailAddress varchar(50),
@MoneyPoolName varchar(50),
@RemainingValue money,
@WarnIfBelowValue money
AS
BEGIN
-- Form the email that will be sent to the recipient, notifying them
-- that the voucher fund has gone below the warning level they specified.
DECLARE @msgBody varchar(max),
@subjectLine varchar(200)
SET @subjectLine = 'Warning concerning voucher fund ' + @MoneyPoolName
-- Construct message body. It will be an HTML document.
SET @msgBody = '<h1>The Voucher fund '
+ @MoneyPoolName
+ ' Is Below The Level You Specified.</h1>'
+ '
'
+ '<table border="1" cellpadding="2">'
+ '<tr>'
+ '<th>Your Specified Watch Level</th>'
+ '<th>Actual Remaining Amount</th>'
+ '</tr>'
+ '<tr>'
+ '<td align="center">'
+ '$' + CONVERT(varchar, @WarnIfBelowValue, 1)
+ '</td>'
+ '<td align="center">'
+ '$' + CONVERT(varchar, @RemainingValue, 1)
+ '</td>'
+ '</tr>'
+ '</table>'
-- now send the email
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL Job Profile',
@recipients = @EmailAddress,
@subject = @subjectLine,
@body = @msgBody,
@body_format = 'HTML';
END
I've double checked and I know I have a mail profile called SQL Job Profile.
Any idea why this error should have occurred?
Kindest Regards, Rod Connect with me on LinkedIn.
November 21, 2013 at 12:16 pm
FYI, I've implemented Database Mail on a lot of instances recently and I've never had to restart SQLAgent (caveat: these were mostly 2005, 2008 and 2008 R2 instances).
Assuming you can receive a test message to yourself sent by right-clicking Database Mail/Send Test Message..
1) Verify the the email account is associated to the profile you specified in the job 2) Check the your junk email folder or anti-spam system.
I've been tripped up by anti-spam filters more times that I like to admit. The #3 IT question after "Have you turned it off & on again?" and "Is Caps-Lock on?" ought to be "Have you looked in your Junk email folder?"
November 21, 2013 at 12:50 pm
Well, I've got to admit that I haven't performed a test message in a while. I just did, and the email never came to me. I even checked my junk folder - nothing. I clicked on the Troubleshoot button, but that brought me to a page for Visual Studio 2013; I don't find that to be of much help.
Kindest Regards, Rod Connect with me on LinkedIn.
November 21, 2013 at 3:10 pm
Then Right-Click Database Mail/View Database Mail Log and see if there's an error that holds clues. In my expereince, when I can't send a test message it's one of 3 things...
1) Something wrong in the Database Mail Account configuration. Check with your email server person to make sure the server name, Port Number, SSL and SMTP Authentication settings are correct.
2) Anti-virus. Check anti virus software for settings that prevent the server from being hijacked to send spam.
3) Email server security. Ask your email server person to look for anti-spam security settings that prevent the database server from connecting to the email server. I had to go through this process with my mail server guy mutliple times to enable all of our SQL instances to send mail.
Once you can send a test message, xp_sendmail will work if you have the right parameters.
November 22, 2013 at 7:41 am
WOW, I think that was it! I didn't know that you could right-click on Database Mail to get anything. Sure enough, that's what I did and there was lots of red flags! All saying that I wasn't using SSL. I got back into configuration for the relevant account, set it to use SSL, ran the test and finally got an email response. WOO HOO!
Thank you!!
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply