September 16, 2008 at 11:31 am
I am tasked to reimport from our MS SQL Server 2005 database back down to the original source which is MS SQL Server 2000. I am using SSIS to perform the data pump. The destination MS 2000 is a replication publisher that has many subscribers. I was wondering if there is a way to disable the replication jobs so that I can perform my database pump from 2005 back down to 2000. I have been working with the approach of disabling the job through the system stored procedure sp_update_job. I have tried this approach but it isn't flipping the enabled flag (TinyInt) to 0 which is disabled. The ultimate goal is to disable replication while pumping the data and then re-enable the replication. I have build a stored procedure that will populate all of the active scheduled job into a temporary table then I wold like to execute the sp_update_job stored procedure to disable all of the jobs and then after the data pump I will join back to the tempery table to re-enable all of the jobs. I don't want to disable the flag right in the sysJobSchedules table because I have looked at the system stored procedure and there are more table affected in the scheduling of the jobs. I'm note too sure if this is the best approach so I am open to any ideas.
EXECUTE msdb..sp_update_job @Job_Id,@enabled = 0
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 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply