iVend 6.6 integrated with SAPB1
Query for generate the email message which SQL job got executed or not
NA
We can use below query to send email message which SQL job got executed or not
declare @lFailedJobCount int
set @lFailedJobCount = 0
select MAX(run_date), (case sh.run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
end) as RunStatus, j.name
from msdb.dbo.sysjobs j , msdb.dbo.sysjobhistory sh
where sh.job_id = j.job_id
and sh.step_id = 0
group by j.name, run_status
having sh.run_status = 0
select @lFailedJobCount =@@ROWCOUNT
if @lFailedJobCount > 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'IT Support',
-- Give recipient email address
@recipients = 'abc@citixsys.com',
@body = 'SQL Job Status: ',
@query = 'select MAX(run_date), (case sh.run_status
when 0 then ''Failed''
when 1 then ''Succeeded''
when 2 then ''Retry''
when 3 then ''Canceled''
when 4 then ''In progress''
end) as RunStatus, j.name
from msdb.dbo.sysjobs j , msdb.dbo.sysjobhistory sh
where sh.job_id = j.job_id
and sh.step_id = 0
group by j.name, run_status;',
@subject = 'Issue in running sql jobs!',
@execute_query_database = 'master';
END
NA