/*=========================================================================================================================
*			             usp_ScheduleMyJob
* Input Parameters: @JobName , @frequency
* Ouput           : Schedules the specified Job to either run daily or 
weekly (on a specified day)
*			Returns 1 on success and -1 on failure
* Description     : This will Schedule a Job that was created using 
usp_Create_MyDB_Backup_Job
*
* Written by :Vijaykumar Aski  	4/23/2001	 			Edited by: Vijaykumar Aski  
	5/1/2001
===========================================================================================================================*/
IF EXISTS (SELECT Name FROM sysobjects WHERE name='usp_ScheduleMyJob' and 
type = 'p')
DROP PROC usp_ScheduleMyJob
go
CREATE PROCEDURE [dbo].[usp_ScheduleMyJob]
					 --Input parameters
	               			@JobName varchar(100),
					@Frequency int, -- 4 for daily , 8 for weekly
					@Day	   int, -- If weekly day of the week 1 -Monday ... 7 -Sunday
					@StartTime int -- in HHMMSS format (24 hours) e.g. 233000 for 11:30 PM

as
SET NOCOUNT ON
DECLARE @Ret_Code int
DECLARE @ScheduleName  varchar(200)
DECLARE @mystepname varchar(200)

set @Ret_code = 1

if @Frequency = 4  -- Backup daily
  begin
   	set @mystepname = @JobName + '_Daily'
  end
else if @Frequency = 8 -- Weekly Backup
begin
      if @Day < 1 or @Day >7
	begin
	      raiserror('Specify a Valid week day to schedule the job (1 to 
7)',18,1)
	      set @Ret_Code = -1
	end
      else
	begin
     	     if @Day = 1  set  @Day =2 	 --Monday
	     else if @Day = 2  set @Day =4 	 -- Tuesday
	     else if @Day = 3  set @Day =8    -- Wednesday
	     else if @Day = 4  set @Day =16    -- Thursday
	     else if @Day = 5  set @Day =32    -- Friday
	     else if @Day = 6  set @Day =64    --Saturday
	     else if @Day = 7  set @Day =1   -- Sunday
	end

      set @mystepname = @JobName + '_Weekly'
end
else
begin
      raiserror('Invalid Job frequency',18,1)
       set @Ret_Code = -1
end

if @Ret_Code <> -1
  begin
        EXEC msdb.dbo.sp_add_jobschedule @job_name = @JobName,
   		@name = @mystepname,
		@freq_type = @Frequency,
		@freq_interval = @Day, -- Indicates every Saturday
		@Active_Start_date = 20010423,
		@active_start_time = @StartTime, -- Will start just before midnight on 
saturday
		@freq_recurrence_factor = 1,
		@Active_End_Date = 30001231,
		@active_end_time = 220000

  end

return @Ret_Code

SET NOCOUNT OFF
go





-- Usage
-- exec usp_Create_MyDB_Backup_Job 'MyDB', 
'd:\mssql7\Backup','MyDB_Backup_On_April26','MyDB_Backup'

-- exec usp_ScheduleMyJob 'MyDB_Backup',8,6,235959

-- EXEC sp_add_jobserver @job_name = 'MyDB_BackupJob', @server_name = 
N'(local)'


--Schema for Audit table, which I have used, this is a generic audit table 
which I use for backup also



if exists (select * from sysobjects where Id = 
object_Id(N'[dbo].[tblSysAudit]') and OBJECTPROPERTY(Id, N'IsUserTable') = 
1)
drop table [dbo].[tblSysAudit]
GO
Create table [dbo].[tblSysAudit]
(
SysAuditId	        	sl_Id IDENTITY(1,1) NOT FOR REPLICATION,
Actiontypeid			sl_Id,
ModifiedUserId  		sl_UserId,
Classification  		sl_Desc,
ItemInvolved			sl_Desc,
AffectedValue			sl_Desc null,
NewValue        		sl_Comments null,
PreviousValue   		sl_Comments null,
ModifiedDate			sl_DATE DEFAULT getdate()
)
GO