November 13, 2015

Send Sql server agent job notifications Via Sql Script

Today I have to add some notifications about the status of specific sql server agent jobs, specially if the job fail and been a lazy programmer I create some metadata tables with specific messages and jobs, but I still want to share the core code used to send this message. Because this is set on a ssis sql task we only care to see a message if the job fails so, top 50 should be enough history to know something is wrong :-) .
Cheers.
declare @sqlcmd varchar(2000) = 'set transaction isolation level read uncommitted;
set nocount on; declare @today varchar(8) = convert (varchar(8),getdate(),112)
SELECT  top 50  j.name JobName ,
        h.step_name StepName ,
        CONVERT(CHAR(10), CAST(STR(h.run_date, 8, 0) AS DATETIME), 111) RunDate ,
        STUFF(STUFF(RIGHT(''000000'' + CAST (h.run_time AS VARCHAR(6)), 6), 5, 0,
                    '':''), 3, 0, '':'') RunTime ,
        h.run_duration StepDuration ,
        CASE h.run_status
          WHEN 0 THEN ''failed''
          WHEN 1 THEN ''Succeded''
          WHEN 2 THEN ''Retry''
          WHEN 3 THEN ''Cancelled''
          WHEN 4 THEN ''In Progress''
        END AS ExecutionStatus ,
        h.message MessageGenerated
FROM    sysjobhistory h
        INNER JOIN sysjobs j ON j.job_id = h.job_id
where j.name = ''Job_name Here''
and h.run_status in(1,0,3) and h.run_date >= @today
ORDER BY h.instance_id desc',
@today varchar(50) = 'NameOfAttachFileHere';
set @today = @today+'_'+ replace (replace(replace(convert(varchar(25),
getdate(),
120),
'-',
''),
':',
''),
' ',
'') +'.csv';
EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'EmailRecipientsHere',
               @body= 'EmailBodyHere',
               @execute_query_database = 'msdb',
    @query = @sqlcmd,
    @subject = 'Subject Alert Here',
               @attach_query_result_as_file = 1,
               @query_result_separator = ',
',
               @query_result_no_padding = 1,
               @query_attachment_filename = @today;

No comments:

Post a Comment

Contact Form

Name

Email *

Message *