September 16, 2008 at 11:47 am
That is exactly what I did and it worked fine. The following code is the brunt of what I used to get this working. Hope it helps. In your case you might have multiple subscribers and the ID will be different than what I have in the query so that will have to be adjusted.
DECLARE
@JOBNAME VARCHAR(64)
, @SCHEDULEID INT
, @FREQTYPE INT
DECLARE CUR_JOB_NAME CURSOR FOR
SELECT
DA.NAME
, SS.SCHEDULE_ID
, SS.FREQ_TYPE
FROM
DISTRIBUTION.DBO.MSDISTRIBUTION_AGENTS DA JOIN MSDB..SYSJOBS SJ
ON DA.JOB_ID = SJ.JOB_ID
JOIN MSDB..SYSJOBSCHEDULES SJS
ON SJS.JOB_ID = SJ.JOB_ID
JOIN MSDB..SYSSCHEDULES SS
ON SJS.SCHEDULE_ID = SS.SCHEDULE_ID
WHERE
SUBSCRIBER_ID = 21
ORDER BY
DA.NAME
OPEN CUR_JOB_NAME
FETCH NEXT FROM CUR_JOB_NAME INTO @JOBNAME, @SCHEDULEID, @FREQTYPE
WHILE @@FETCH_STATUS = 0
BEGIN
/*
Test FREQ_TYPE to see if this is a job that runs continuously. See BOL
reference for sysschedules table for details. Actions required to "pause"
replication vary based on the replication job schedule.
*/
IF @FREQTYPE = 64
BEGIN
EXEC MSDB..sp_stop_job @JOBNAME
EXEC MSDB..sp_update_job @JOB_NAME = @JOBNAME, @ENABLED = 0
END
IF @FREQTYPE != 64
BEGIN
EXEC MSDB..sp_update_schedule @SCHEDULE_ID = @SCHEDULEID, @ENABLED = 0
END
FETCH NEXT FROM CUR_JOB_NAME INTO @JOBNAME, @SCHEDULEID, @FREQTYPE
END
CLOSE CUR_JOB_NAME
DEALLOCATE CUR_JOB_NAME
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply