Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Not getting the email notification that I set up, part 2 Expand / Collapse
Author
Message
Posted Wednesday, October 30, 2013 8:11 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #1509798
Posted Wednesday, October 30, 2013 9:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 21, 2014 5:35 AM
Points: 69, Visits: 565
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!
Post #1509832
Posted Wednesday, October 30, 2013 12:45 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #1509910
Posted Wednesday, October 30, 2013 2:07 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:37 PM
Points: 2,854, Visits: 3,039
You will also need to restart the SQL Agent in order for the new mail settings to take affect.
Post #1509934
Posted Thursday, October 31, 2013 8:09 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
I was not aware of the fact that I'd have to restart SQL Agent, before it would see the changes that I made to the Database Mail profile. I've just done that. Will have to wait until tomorrow to see if it worked.

Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1510193
Posted Friday, November 1, 2013 8:42 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #1510615
Posted Friday, November 1, 2013 9:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 21, 2014 5:35 AM
Points: 69, Visits: 565
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.
Post #1510638
Posted Friday, November 1, 2013 9:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:37 PM
Points: 2,854, Visits: 3,039
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.
Post #1510654
Posted Friday, November 1, 2013 12:53 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
That's great idea, liteswitch and JeremyE. I've just setup a simple job to do a SELECT '1', put a notification onto it and it will fire in 10 minutes. Will let you know how it goes.

Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1510767
Posted Thursday, November 21, 2013 9:45 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #1516493
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse