Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Not getting the email notification that I set up, part 2


Not getting the email notification that I set up, part 2

Author
Message
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1133 Visits: 1932
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,RodConnect with me on LinkedIn.
liteswitch
liteswitch
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 598
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!
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1133 Visits: 1932
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,RodConnect with me on LinkedIn.
JeremyE
JeremyE
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3724 Visits: 4029
You will also need to restart the SQL Agent in order for the new mail settings to take affect.
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1133 Visits: 1932
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,RodConnect with me on LinkedIn.
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1133 Visits: 1932
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,RodConnect with me on LinkedIn.
liteswitch
liteswitch
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 598
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.
JeremyE
JeremyE
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3724 Visits: 4029
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.
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1133 Visits: 1932
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,RodConnect with me on LinkedIn.
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1133 Visits: 1932
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,RodConnect with me on LinkedIn.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search