Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    191

    Unanswered: Question about Replication

    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?



    Lystra

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My 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

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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:

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •