WEB LISTING 1: Stored Procedure That Performs Log Reader Agent Scheduling

IF EXISTS (SELECT * FROM sysobjects WHERE id = 
object_id(N'[dbo].[sp_SetLogReaderSchedule]') AND OBJECTPROPERTY(id, N'IsProcedure') = 
1)
DROP PROCEDURE [dbo].[sp_SetLogReaderSchedule]
GO

CREATE PROCEDURE sp_SetLogReaderSchedule
@LocalDBName varchar(255) = NULL,
@StartTime int = 0,
@Interval int = 0,
@UOM int = 4

AS 

/*========================================================================
=========
Description: LogReader Agent Schedule Update to run at specified intervals, or     
             Reset to run Continuously if no StartTime is specified                
                                                                                   
             If only StartTime is given, Interval will default to 15 minutes.      
                                                                                   
Usage: sp_SetLogReaderSchedule @DBName, [@StartTime], [@Interval]                  
Formats: @StartTime: HHMMSS, @Interval: [1-1440]                                   
                                                                                   
=========================================================================
==========
Version: 1.02                                                                      
Created: November 2001                                                             
Updated: March    2002  - schedules and stop/start the job if running continuously
                        - check if the database entered is replicated or not 
=========================================================================
==========
Disclaimer:                                                                        
This free code has been tested to the best of our ability, and if you like it, you can 
use it at your own risk. The developer is not liable for any possible damage or loss
of data that this code might incur. Please ensure you understand it and take full   
responsibility before using it.                                                    
=========================================================================
==========*/

BEGIN
SET nocount ON

DECLARE @ServerName varchar (255)
DECLARE @JobID binary(16)  
DECLARE @JobName varchar (255)
DECLARE @FrequencyType int    
DECLARE @ReturnCode int    
DECLARE @Description varchar (255)
DECLARE @RetStat int

DECLARE  @prev_freq_type         int

DECLARE  @freq_type              int
DECLARE  @freq_interval          int
DECLARE  @freq_subday_type       int
DECLARE  @freq_subday_interval   int
DECLARE  @freq_relative_interval int
DECLARE  @freq_recurrence_factor int
DECLARE  @active_start_date      int
DECLARE  @active_end_date        int
DECLARE  @active_start_time      int
DECLARE  @active_end_time        int

  -- Set the return codes to false by default.
  SET @ReturnCode = 1
  SET @RetStat = 1


  -- Validate input values.
  IF (@LocalDBName is NULL)
    BEGIN
     PRINT 'Usage: sp_SetLogReaderSchedule LocalDBName, [StartTime], [Interval]  
'+Char(13)+'StartTime format: ''hhmmss'', Interval: 1 TO 1440, 
eg:'+Char(13)+'sp_SetLogReaderSchedule ''DatabaseName'', ''000100'', ''15'' '+Char(13)+'Starts 
at 1 minute after midnight and has 15 minutes recurrence.'
     RETURN
    END

  -- Check whether the database entered is replicated.
  IF NOT (@LocalDBName IN (SELECT name FROM master.dbo.sysdatabases WHERE 
category = 1))
    BEGIN
     PRINT 'The database entered as parameter is not a replicated database. Please enter a 
different DatabaseName...'
     RETURN
    END 


  -- Remove any leading or trailing spaces from parameters.
  SELECT @LocalDBName = LTRIM(RTRIM(@LocalDBName))

  -- Set defaults.
  SET @ServerName = (SELECT @@servername)

  IF ((@StartTime > 0) AND (@Interval = 0)) SET @Interval = 15 -- 15 minutes by default.

  IF ((@StartTime = 0) AND (@Interval = 0)) SET @FrequencyType = 64 -- If no time specified, 
set the schedule to run continuously.
  ELSE SET @FrequencyType = 4 -- Set the schedule to run hourly.

  -- Validate interval parameter.
  IF (@FrequencyType <> 64) AND (@UOM = 4) AND ((@Interval < 1) OR (@Interval > 1440))
    BEGIN
     PRINT 'The Unit of measure is Minutes. You can only specify an Interval value between 1 and 
1440'
     RETURN
    END
  IF (@FrequencyType <> 64) AND (@UOM = 8) AND ((@Interval < 1) OR (@Interval > 24))
    BEGIN
     PRINT 'The Unit of measure is Hours. You can only specify an Interval value between 1 and 
24'
     RETURN
    END

  -- Get the JobID for the Log Reader Agent only.
  SELECT  @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name LIKE @ServerName+'-'+@LocaldbName+'-[0-9]%') 

  -- Get the JobName for the Log Reader Agent only.
  SELECT  @JobName = name     
  FROM   msdb.dbo.sysjobs    
  WHERE (name LIKE @ServerName+'-'+@LocaldbName+'-[0-9]%') 

  -- Display schedule information.
  SELECT 
@freq_type=freq_type,@freq_interval=freq_interval,@freq_subday_type=freq_subday_type,@fre
q_subday_interval=freq_subday_interval,@freq_relative_interval=freq_relative_interval,@freq_re
currence_factor=freq_recurrence_factor,
         
@active_start_date=active_start_date,@active_end_date=active_end_date,@active_start_time=
active_start_time,@active_end_time=active_end_time
  FROM msdb.dbo.sysjobschedules
  WHERE job_id =  @JobID
  EXEC msdb.dbo.sp_get_schedule_description 
@freq_type,@freq_interval,@freq_subday_type,@freq_subday_interval,@freq_relative_interval,
@freq_recurrence_factor,@active_start_date,@active_end_date,@active_start_time,@active_en
d_time,@Description Output
  PRINT 'Previous Schedule: '+@Description+Char(10)+Char(13)

  -- Stop the job only if it was running continuously before, and update the schedule.
  SET @prev_freq_type = @freq_type

  IF (@prev_freq_type = 64) and (@FrequencyType <> 64)
  BEGIN
     EXEC @RetStat = msdb.dbo.sp_stop_job @job_id = @JobID
      IF @RetStat = 0
        PRINT 'Job Stopped.'+Char(10)+Char(13)
      ELSE
        PRINT 'Error in Stopping the job.'+Char(10)+Char(13)
  END

  -- Schedule the job.
  EXEC @ReturnCode = msdb.dbo.sp_update_jobschedule 
		@job_id = @JobID, 
  		@name = N'Replication Agent Schedule.', /* Set name. Always the same for the 
Replication Agent schedules. */
		/* @new_name = 'Replication Agent Schedule for database'+@LocaldbName. */
		@enabled = 1, 
		@freq_type = @FrequencyType,		/* 4 = Hourly. 64 = Continuous; no 
parameters needed after that. */
		@freq_interval = 1, 			/* Every 1 hour (1 day, 1week, depends on 
@freq_type) */
		@freq_subday_type = @UOM,		/* Occurs every: 8 = "hours intervals", 4 = 
"minutes interval" */
		@freq_subday_interval = @Interval,	/* Every "n" hours or minutes depending on the 
above UOM setting. */
		@freq_relative_interval = 0, 
		@freq_recurrence_factor = 0, 
		@active_start_date = 19900101,		/* Default start date in the past. */
		@active_end_date = 99991231, 		/* 99991231 is interpreted as 'No end 
Date.' */
                @active_start_time = @StartTime,	/* Set the start time. For example, 000100 is one 
minute after midnight. */
                @active_end_time = 235959		/* Defaults to 1 minute before midnight. */

IF @ReturnCode=0
 BEGIN
  -- Display schedule information.
  SELECT 
@freq_type=freq_type,@freq_interval=freq_interval,@freq_subday_type=freq_subday_type,@fre
q_subday_interval=freq_subday_interval,@freq_relative_interval=freq_relative_interval,@freq_re
currence_factor=freq_recurrence_factor,
         
@active_start_date=active_start_date,@active_end_date=active_end_date,@active_start_time=
active_start_time,@active_end_time=active_end_time
  FROM msdb.dbo.sysjobschedules
  WHERE job_id =  @JobID
  EXEC msdb.dbo.sp_get_schedule_description 
@freq_type,@freq_interval,@freq_subday_type,@freq_subday_interval,@freq_relative_interval,
@freq_recurrence_factor,@active_start_date,@active_end_date,@active_start_time,@active_en
d_time,@Description Output
    PRINT 'Schedule Update SUCCEEDED - LogReader Agent '+@JobName+Char(10)+Char(13)
    PRINT 'Current Schedule: '+@Description
 END
ELSE
 PRINT 'Updating LogReader Agent '+@JobName+' Schedule FAILED'

  -- Start the job only if it was scheduled before and now runs continuously.
  IF (@prev_freq_type <> 64) and (@FrequencyType = 64)
  BEGIN
     EXEC @RetStat = msdb.dbo.sp_start_job @job_id = @JobID
      IF @RetStat = 0
        PRINT 'Job Started.'+Char(10)+Char(13)
      ELSE
        PRINT 'Error in Starting the job.'+Char(10)+Char(13)
  END

END

GO