Technical Article

Html mail report of failed jobs (everyday)

,

this script send an HTML email every day with information about failed jobs . the script use the persist.mail to send the report, but you can use another mail component with support html.

/* procedimiento para el informe diario de qué jobs y en qúe pasos han fallado*/CREATE PROCEDURE job_history


AS 
declare @cuerpomail as nvarchar(4000)
declare @tablamail as nvarchar(4000)
declare @nombrejob as nvarchar(500)
declare @server as nvarchar(500)
declare @jobid as nvarchar(500)
declare @status as nvarchar(100)
declare @fecha as nvarchar(50)
declare @paso as nvarchar(100)
declare @mensaje as nvarchar(500)
declare @pasoid as nvarchar(10)
declare @fechahoy as smalldatetime
declare @anno as nvarchar(10)
declare @mes as nvarchar(10)
declare @dia as nvarchar(10)


declare @registro as nvarchar(100)


--variables para mail
declare @sujetomail as nvarchar(400)
declare @hr int
declare @mail int
---------------------------------
/**********************************************************************************//*obtengo la fecha y la descompongo para pasarla como  'yyyymmdd' *//**********************************************************************************/
set @fechahoy=getdate()
set @anno=year(@fechahoy)
set @mes=month(@fechahoy)
set @dia=day(@fechahoy)

if len(@dia)=1
begin
set @dia='0'+@dia

end 

if len(@mes)=1 
begin
set @mes='0'+@mes
end

print 'año actual: ' +@anno
print 'mes actual: '+@mes
print 'dia actual: '+@dia

/*******************************************************************************/

set @registro=''



set @tablamail='<table align=center border=1 width=80% bordercolor=black>'
set @tablamail=@tablamail+'<tr><td>'
set @tablamail=@tablamail+'<tr bgcolor=white><td align=center colspan=3><img src="cid:sqlserver" align="absolutemiddle">  <b>Reporte  '+cast(getdate() as nvarchar(50))+'</b></td></tr>'


declare Cenvio cursor local for 
SELECT J.name,JH.server,JH.job_id,  
CASE run_status
WHEN 0 THEN 'fallado'
WHEN 1 THEN 'Terminado'
ELSE '???'
END as run_status, run_date, step_id, step_name, message

FROM         msdb.dbo.sysjobhistory  JH
inner join  msdb.dbo.sysjobs  J ON JH.job_id = J.job_id

where run_status=0  and run_date=@anno+@mes+@dia
group by J.name,JH.job_id, JH.run_date, JH.server,JH.run_status , JH.step_id, JH.step_name,JH.message
order by run_date desc

open Cenvio
fetch  next from Cenvio into @nombrejob,@server,@jobid,@status,@fecha,@pasoid,@paso,@mensaje
while @@fetch_status=0
begin

if @nombrejob<>@registro
begin
if @pasoid='0'
begin
print @nombrejob
end
else
begin
print @nombrejob+'---('+@pasoid+')'+@paso
end


set @tablamail=@tablamail+'<tr bordercolor=#FBEDBB><td colspan=2 bgcolor=#ffe4b5><img src="cid:failed" > '
set @tablamail=@tablamail+'<font color=red><b>'+@nombrejob+'</font></b> '
set @tablamail=@tablamail+'</td></tr>'


end
else
begin
if @pasoid='0'
begin
print '---'
end
else
begin
print '---------------------------('+@pasoid+')'+@paso
end

set @tablamail=@tablamail+'<tr bordercolor=#FBEDBB valign="top"><td width="40%">'
set @tablamail=@tablamail+'<font size=-1>Paso ('+@pasoid+') <br>'+@paso+'</font> '
set @tablamail=@tablamail+'</td>'
set @tablamail=@tablamail+'<td>'
set @tablamail=@tablamail+'<font size=-2>'+@mensaje+'</font></td></tr>'


end

set @registro=@nombrejob
fetch next from Cenvio into @nombrejob,@server,@jobid,@status,@fecha,@pasoid,@paso,@mensaje

end


close Cenvio
deallocate Cenvio



set @tablamail=@tablamail+'</table>'

set @cuerpomail='<HTML><BODY bgcolor=lightgoldenrodyellow><center><b><img src="cid:logo" alt="Misco Online"></b><br></center>'
set @cuerpomail=@cuerpomail+@tablamail

exec @hr = sp_OACreate 'Persits.MailSender', @Mail OUT
exec @hr=sp_OAsetproperty @Mail,'host','xxx.xxx.xxx.xxx'
exec @hr=sp_OASetProperty @Mail, 'fromName','XXXXXX'
exec @hr = sp_OASetProperty @Mail, 'Subject', '(JOB) Reporte JOBS FALLIDOS'

exec @hr=sp_OAsetproperty @mail,'from','xxx@xxx.com'
exec @hr=sp_OAsetproperty @Mail,'ishtml','1'
exec @hr = sp_OAMethod @Mail, 'AddBcc', NULL, 'xxx@xxx.com'
exec @hr=sp_OAmethod @mail, 'AddEmbeddedImage',null,'d:\InetPub\Intranet\images\LogoMiscoIntranet.jpg','logo'
exec @hr=sp_OAmethod @mail, 'AddEmbeddedImage',null,'d:\InetPub\Intranet\images\failed.gif','failed'
exec @hr=sp_OAmethod @mail, 'AddEmbeddedImage',null,'d:\InetPub\Intranet\images\sqlserver.jpg','sqlserver'
exec @hr=sp_OASetProperty @mail,'body',@cuerpomail

exec @hr = sp_OAMethod @Mail, 'Send'
GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating