/*
复制分发执行的是job,控制复制分发就是控制这些job的开关。

--启动合并复制所有同步
EXEC ReplControl @start = 1
--关闭所有同步
EXEC 合并复制ReplControl @start = 0
*/

CREATE PROCEDURE ReplControl
@start INT --是否启动：0停止，1开启
AS
BEGIN
	DECLARE @execution_status INT,@sql VARCHAR(1000)

	/*停止时，查询正在执行的job,开启时，查询停止的job
	@execution_status:
	1 正在执行。
	4 空闲。
	*/
	SET @execution_status = CASE(@start) WHEN 0 THEN 1 ELSE 4 END
	SET @sql = CASE(@start) WHEN 0 THEN 'msdb.dbo.sp_stop_job @job_id = '
							ELSE 'msdb.dbo.sp_start_job @job_id = ' END 

	CREATE TABLE #enum_job (
	Job_ID uniqueidentifier,
	Last_Run_Date int,
	Last_Run_Time int,
	Next_Run_Date int,
	Next_Run_Time int,
	Next_Run_Schedule_ID int,
	Requested_To_Run int,
	Request_Source int,
	Request_Source_ID varchar(100),
	Running int,
	Current_Step int,
	Current_Retry_Attempt int,
	State int
	)
	insert into #enum_job
	exec master.dbo.xp_sqlagent_enum_jobs 1,hello
		 /*p_sqlagent_enum_jobs <is sysadmin (0 or 1)>,
						  <job owner name>
						  [, <job id>]

	The first parameter identifies whether you want to return information about all jobs on the server, or just jobs owned by a particular job owner. If you specify "0" for this first parameter, it means you want to return job information for a particular job owner. If you specify a "1," it means you want information for all jobs. The second parameter identifies the job owner. This parameter is required on all calls to this XP but is only used when you specify "0" for the first parameter. The third and last parameter only needs to be provided if you want to return information about a particular job_id. */

	DECLARE @Job_ID uniqueidentifier
	DECLARE @item_table TABLE (Job_ID uniqueidentifier)
	INSERT INTO @item_table(Job_ID)
	SELECT A.Job_ID
	FROM #enum_job a
	JOIN msdb.dbo.sysjobs j ON a.Job_ID = j.job_id
	JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
	WHERE c.name = 'REPL-Merge' AND a.State = @execution_status

	DECLARE @tmpSql VARCHAR(1000)
	WHILE (SELECT COUNT(1) FROM @item_table) > 0
	BEGIN
		SELECT TOP 1 @Job_ID = Job_ID
		FROM @item_table
		SET @tmpSql = @Sql + QUOTENAME(@Job_ID,'''')
		EXEC ( @tmpSql )
		--PRINT @Job_ID

		DELETE FROM @item_table
		WHERE @Job_ID = Job_ID
	END
	DROP TABLE #enum_job
END 

【版权声明】
本站部分内容来源于互联网，本站不拥有所有权，不承担相关法律责任。如果发现本站有侵权的内容，欢迎发送邮件至masing@13sy.com 举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。