CREATE PROCEDURE DBA_JobStop_CT AS BEGIN DECLARE @JobName NVARCHAR(100) DECLARE @sql NVARCHAR(500) DECLARE JobStop CURSOR FOR SELECT j.name AS job_name FROM msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id 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 session_id DESC) AND j.name IN ('ETL_Replication_AdayMaster','ETL_Replication_BasvuruMaster') AND DATEDIFF(HOUR,ja.start_execution_date,GETDATE())>=3 AND start_execution_date is not null AND stop_execution_date is null OPEN JobStop FETCH NEXT FROM JobStop INTO @Jobname WHILE (@@FETCH_STATUS=0) BEGIN SET @sql=N'EXECUTE [KNETETL].[msdb].[dbo].[sp_stop_job] N'''+@JobName+''''
FETCH NEXT FROM JobStop INTO @JobName --PRINT(@sql) EXECUTE (@sql) END CLOSE JobStop; DEALLOCATE JobStop; END
