CREATE PROC P_STOPJOB
AS
BEGIN
DECLARE @I INT
DECLARE @JOBID UNIQUEIDENTIFIER
-- 1. CREATE TEMP TABLE TO SAVE JOBS STATUS
CREATE TABLE #JOB_RUN_STATUS
(
JOB_ID UNIQUEIDENTIFIER NOT NULL,
LAST_RUN_DATE INT NOT NULL,
LAST_RUN_TIME INT NOT NULL,
NEXT_RUN_DATE INT NOT NULL,
NEXT_RUN_TIME INT NOT NULL,
NEXT_RUN_SCHEDULE_ID INT NOT NULL,
REQUESTED_TO_RUN INT NOT NULL, -- BOOL
REQUEST_SOURCE INT NOT NULL,
REQUEST_SOURCE_ID SYSNAME COLLATE DATABASE_DEFAULT NULL,
RUNNING INT NOT NULL, -- BOOL
CURRENT_STEP INT NOT NULL,
CURRENT_RETRY_ATTEMPT INT NOT NULL,
JOB_STATE INT NOT NULL
)
-- 2. GET JOBS STATUS
INSERT INTO #JOB_RUN_STATUS
EXECUTE MASTER.DBO.XP_SQLAGENT_ENUM_JOBS 1, 'SA'
-- 3. GET RUNNING JOBS
SELECT JOB_NAME = J.NAME
,S.*,ROW_NUMBER() OVER (ORDER BY J.NAME) AS ID
INTO #RUNJOB
FROM #JOB_RUN_STATUS S
INNER JOIN MSDB.DBO.SYSJOBS J
ON S.JOB_ID = J.JOB_ID
WHERE S.RUNNING = 1 -- RUNNING = 1
--4.停止运行作业
SELECT @I=COUNT(*) FROM #RUNJOB
WHILE @I>0
BEGIN
SELECT @JOBID=JOB_ID FROM #RUNJOB WHERE ID=@I
EXEC MSDB.DBO.SP_STOP_JOB '',@JOBID
SET @I=@I-1
END
END