MSSql的复写很方便，但由于线路或服务器等的不确定因素，经常导致复写会停止，如果前台应用程序没有这方面的检查，订阅方是很难发现的，下面说下，如何在订阅方在数据库上设置自动检查订阅状态及在停止状态下，自动重新启动：1.要明白订阅就是SQL执行Agent的jobs，因此需要找出订阅对应的job，如果复写停了，可以通过重新启动这个任务来启动复写。2.MS有个隐藏的系统存储过程'sp_MSget_jobstat'，可以判断任务的状态。3.根据'sp_MSget_jobstat'新建一个自定义的存储过程USE MSDB; ----注意需要使用MSDB这个系统数据库GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='auto_sp_MSget_jobstate'; AND TYPE='P')DROP PROC; auto_sp_MSget_jobstateGO
CREATE PROCEDURE auto_sp_MSget_jobstate@job_id; UNIQUEIDENTIFIER,@jobname NVARCHAR(50)ASBEGINDECLARE @is_sysadmin INTDECLARE @job_ownersysnameDECLARE @job_stateINT DECLARE @job_id_as_char VARCHAR(36)
SET NOCOUNT ON
CREATE TABLE #xp_results (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)
-- Need a job_idif (@job_id IS NULL)BEGIN SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id) RAISERROR(14262, -1, -1, '@job_id', @job_id_as_char) RETURN(1) -- FailureEND
-- Capture job execution information (for local jobs only since that's all SQLServerAgent caches)SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)SELECT @job_owner = suser_sname(suser_sid())INSERT INTO #xp_resultsEXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
-- Select the job state of the job in questionSELECT @job_state = job_state FROM #xp_results WHERE @job_id = job_id
-- Error if we have no rows selectedif (@job_state IS NULL)BEGIN SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id) RAISERROR(14262, -1, -1, '@job_id', @job_id_as_char) RETURN(1) -- FailureENDELSE-- SELECT @job_stateif @job_state<>1 ---BEGINEXEC sp_start_job @job_name = @jobnameEND-- All doneDROP TABLE #xp_resultsRETURN(0) -- SuccessEND
4.将这个新的存储过程放到Agent中定制自动执行就可以了，设置执行的语句应该是：exec; auto_sp_MSget_jobstate; @job_id,@jobname其中@job_id,@jobname 都在[msdb.dbo.sysjobs]可以根据你的订阅任务来确定.
http://www.cnblogs.com/Augur/archive/2006/09/22/512066.html
