SQL Server 作业批量停止

2012/08/02 13:40
阅读数 539

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


展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
1 收藏
0
分享
返回顶部
顶部