Database Mail Not working after SQL job completed.

Question

Post reply

Database Mail Not working after SQL job completed.

SQlLogic

SSCrazy Eights

Points: 9686
More actions
March 19, 2014 at 2:30 pm

#286833

I am running script from SQL agent which includes Database mail script also when job is running successfully no e-mail notification send from SQL job or Database mail.
When I am running this script from SSMS I do get mail notification but from SQL agent not getting .
Running on SQL 2008R2 , Also Database mail profile is enable in SQL agent properties.
if @@ROWCOUNT > 0
begin
declare @emailSubject varchar(100),
@columnHeaders varchar(1000),
@tableHTML nvarchar(max)
select @emailSubject = 'My Results Email', -- Subject of Email
@columnHeaders = xxxx</th><th>Customer</th><th>xxxxx</th><th>xxxxx</th><th>OpenedBy</th><th>xxxxxo</th><th>xxxxxxxxxx</th><th>xxxxxxl</th><th>xxxxxxxx</th><th>xxxxxxxxxx' -- Column headers (must put </th><th> between each header)
set @tableHTML =
'<div><b>Magic- Open Tickets</b></div>
' + -- This is the bold text at the top of your email
'<table border="1" cellpadding="5"><font face="Calibri" size=2>' +
'<tr><th>' + @columnHeaders + '</th></tr>' +
convert(nvarchar(max),
(
SELECT td = Tickets, '',
td = ISNULL(xxxxxx,'x'), '',
td = ISNULL(xxxxxx,'x'), '',
td = ISNULL([xxxxxxx], 'x'), '',
td = ISNULL(xxxxxxxxx,'x'), '',
td = ISNULL(xxxxxxxx,'x'), '',
td = ISNULL(xxxxxxx,'x'),'',
td = ISNULL(xxxxxxx, 'x'),'',
td = ISNULL(xxxxxxxxxxx, 'x') ,'',
td = ISNULL([Description],'x')
from master.dbo.xxxxxxxxxxx
for xml path('tr'), type
)) +
'</font></table>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL-MailAlerts',
@recipients='xxxxxxxxxxxxxxxx',
@subject = 'xxxxxxxxxxxxxxxxxxxxx',
@body = @tableHTML,
@body_format = 'HTML' ;
end
Any workaround please?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

first, make sure it hit the queue,a dn see if there is an error in the mail logs.

do you see your email in this select?

SELECT

sent_status As Reason,

err.[description],

mail.*

FROM [msdb].[dbo].[sysmail_allitems] mail

inner join [msdb].[dbo].[sysmail_event_log] err

ON err.mailitem_id = mail.mailitem_id

order by mailitem_id desc

my most common error is

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2014-03-19T07:33:46). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it 74.125.196.16:587).

after speaking with the network guys, i found that the firewall blocks all servers from sending to ports 25/825/587, to any ip address, unless the server is whitelisted in a number of places on our network.

the target machine actively refused , from experience, is a firewall issue, if that helps.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 2

looks like nothing is happening after running job.. query you sent me showing 2 days earlier info only.

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 3

same query works fine from SSMS ..I get query result in e-mail . But when I am running through SQL job no notification of query result. and Even not showing any error from the above script you sent to check DB mail log.