SQL Server 启停作业脚本

--操作类型stop停止作业，start启动作业
DECLARE @op VARCHAR(10) = 'stop'
--操作Flag：1解析 2匹配，同时操作做与运算即为
DECLARE @num INT = 0
DECLARE @jobName NVARCHAR(100),@sql1 NVARCHAR(MAX),@sql2 NVARCHAR(MAX)

SELECT @sql1 = (SELECT 'exec msdb.dbo.sp_'+@op+'_job N'''+name+''' '+CHAR(10)
FROM msdb.dbo.sysjobs s WHERE [name] LIKE 'DataCollectionJob%' FOR XML PATH(''))

SELECT @sql2 = (SELECT 'exec msdb.dbo.sp_'+@op+'_job N'''+name+''' '+CHAR(10)
FROM msdb.dbo.sysjobs s WHERE [name] LIKE 'DataMatchingJob%' FOR XML PATH(''))

IF @op = 'stop'
begin
  --判断有没有本来就不启用的
  --SELECT * FROM master.dbo.Sys_CollectionWorkerMacs scwm WHERE enabled=0
  --SELECT * FROM master.dbo.Sys_MatchingWorkerIPs smwi WHERE enabled=0
  IF @num & 1 = 1
  begin
    UPDATE master.dbo.Sys_CollectionWorkerMacs SET enabled=0
  END
  IF @num & 2 = 2
  begin
    UPDATE master.dbo.Sys_MatchingWorkerIPs SET enabled=0
  end
  PRINT '延迟3分钟停止作业…'
  WAITFOR DELAY '00:03:00:00'
END
IF @op = 'start'
BEGIN
  IF @num & 1 = 1
  begin
    UPDATE master.dbo.Sys_CollectionWorkerMacs SET enabled=1
  END
  IF @num & 2 = 2
  begin
    UPDATE master.dbo.Sys_MatchingWorkerIPs SET enabled=1
  end
  PRINT '启动作业…'
END

IF @num & 1 = 1
begin
 exec sys.sp_executesql @sql1
END
IF @num & 2 = 2
begin
  exec sys.sp_executesql @sql2