I have a server that replicate to another machine at 6am, noon and 6pm everyday.  The problem is that on Thursdays it should only replicate at noon.
Is it possible to do this and how?
LystraMy gut feeling is that this ought to be possible, but I've never tried it.  Unfortunately, I don't see much chance of my having time to try it anytime soon, so I hope that someone else knows the answer for you.
-PatP|||Try this idea - two schedules:
-- Script generated on 8/25/2004 4:37 PM
BEGIN TRANSACTION            
  DECLARE @.JobID BINARY(16)  
  DECLARE @.ReturnCode INT    
  SELECT @.ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @.name = N'[Uncategorized (Local)]'
  -- Delete the job with the same name (if it exists)
  SELECT @.JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'test')       
  IF (@.JobID IS NOT NULL)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @.JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''test'' since there is already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @.job_name = N'test' 
    SELECT @.JobID = NULL
  END 
BEGIN 
  -- Add the job
  EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT , @.job_name = N'test', @.owner_login_name = N'NCBI_NT\Lozitski', @.description = N'No description available.', @.category_name = N'[Uncategorized (Local)]', @.enabled = 1, @.notify_level_email = 0, @.notify_level_page = 0, @.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
  IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback 
  -- Add the job steps
  EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 1, @.step_name = N'step', @.command = N'select getdate()', @.database_name = N'master', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 0, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0, @.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
  IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID, @.start_step_id = 1 
  IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback 
  -- Add the job schedules
  EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name = N'one', @.enabled = 1, @.freq_type = 8, @.active_start_date = 20040825, @.active_start_time = 60000, @.freq_interval = 111, @.freq_subday_type = 8, @.freq_subday_interval = 6, @.freq_relative_interval = 0, @.freq_recurrence_factor = 1, @.active_end_date = 99991231, @.active_end_time = 180000
  IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name = N'two', @.enabled = 1, @.freq_type = 8, @.active_start_date = 20040825, @.active_start_time = 115900, @.freq_interval = 16, @.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval = 0, @.freq_recurrence_factor = 1, @.active_end_date = 99991231, @.active_end_time = 235959
  IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback 
  -- Add the Target Servers
  EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID, @.server_name = N'(local)' 
  IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback 
END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave:sql
 
No comments:
Post a Comment