Хранимая процедура
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
USE [msdb] GO /****** Object: StoredProcedure [dbo].[alertjob_test] Script Date: 04/25/2018 08:59:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[alertjob_test] zakupki NVARCHAR(max) AS BEGIN set nocount ON DECLARE @job_id VARCHAR(50) SET @job_id = (SELECT job_id FROM msdb.dbo.sysjobs WHERE [name] = zakupki) IF (not object_id('tempdb..#x') is null) drop table #x SELECT TOP 2 s.instance_id INTO #x FROM msdb.dbo.sysjobhistory s WHERE s.job_id = @job_id AND s.step_name LIKE '(Job outcome)' ORDER BY s.instance_id DESC IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = zakupki) BEGIN IF (not object_id('tempdb..#z') is null) drop table #z SELECT ja.job_id, j.name as job_name, ja.start_execution_date, isNull(last_executed_step_id,0) + 1 as current_executed_step_id, js.step_name INTO #z FROM msdb.dbo.sysjobactivity ja left join msdb.dbo.sysjobhistory jh on ja.job_history_id = jh.instance_id inner join msdb.dbo.sysjobs j on ja.job_id = j.job_id inner join msdb.dbo.sysjobsteps js on ja.job_id = js.job_id and isNull(ja.last_executed_step_id,0) + 1 = js.step_id WHERE ja.session_id = (select top 1 session_id from msdb.dbo.syssessions order by agent_start_date desc) and start_execution_date is not null and stop_execution_date is NULL IF EXISTS ( select job_name from #z where job_name = zakupki ) BEGIN SELECT 1 END ELSE BEGIN IF EXISTS (SELECT s.run_status FROM msdb.dbo.sysjobhistory s WHERE s.job_id = @job_id AND s.instance_id > (SELECT MIN(instance_id) FROM #x) AND s.run_status = 0) BEGIN SELECT 2 END IF ((SELECT TOP 1 s.run_status FROM msdb.dbo.sysjobhistory s WHERE s.job_id = @job_id AND s.step_name LIKE '(Job outcome)' ORDER BY s.instance_id DESC) = 3) BEGIN SELECT 2 END ELSE BEGIN SELECT 0 END END DROP TABLE #x DROP TABLE #z END ELSE BEGIN SELECT 2 END END |