/*

PROJECT:		BAU
AUTHOR:			FRANK BAZAN
DATE:			18/11/2005
DESCRIPTION:		SCRIPT INSERTS CURRENT JOB PARAMETERS FROM MSDB INTO
			THE CORE AGENT_JOB% TABLES

----------------------------------------------------------------------------------
VERS	CHANGE					DATE		BY
-----------------------------------------------------------------------------------
1	1ST VERSION				18/11/05	FRANK BAZAN

2	Added new source table for schedule	22/02/06	Frank Bazan
	meta data. SysSchedules now contains
	the schedule info and sysjobschedules
	associates it with a job.
		

*/

USE Control
GO

INSERT INTO 	Control.dbo.AGENT_JOBS(
		AgentJobName, 
		IsEnabled, 
		AgentJobDescription, 
		StartStepId, 
		CategoryName, 
		OwnerLoginName, 
		NotifyLevelEventlog, 
		NotifyLevelEmail, 
		NotifyLevelNetsend, 
		NotifyLevelPage, 
		NotifyEmailOperatorName, 
		NotifyNetsendOperatorName, 
		NotifyPageOperatorName, 
		DeleteLevel, 
		ServerName )

SELECT		J.name AS job_name, 
		enabled, 
		description, 
		start_step_id, 
		C.Name AS category_name,
		SUSER_SNAME (owner_sid) AS owner_name,
		notify_level_eventlog, 
		NULLIF(notify_level_email,0),
		NULLIF(notify_level_netsend,0),
		NULLIF(notify_level_page,0),
		O1.Name AS email_operator, 
		O2.Name AS netsend_operator, 
		O3.Name AS notify_page_operator, 
		delete_level,
		'(LOCAL)' AS server_name

FROM 		msdb.dbo.sysjobs J

-- LINK CATEGORY
LEFT JOIN 	msdb.dbo.syscategories C
ON		C.category_id = J.category_id

-- LINK EMAIL OPERATOR
LEFT JOIN 	(	SELECT 	Name,
				ID
			FROM 	msdb.dbo.sysoperators ) O1 
ON		O1.ID = J.notify_email_operator_id

-- LINK NETSEND OPERATOR
LEFT JOIN 	(	SELECT 	Name,
				ID
			FROM 	msdb.dbo.sysoperators ) O2
ON		O2.ID = J.notify_netsend_operator_id

-- LINK PAGE OPERATOR
LEFT JOIN 	(	SELECT 	Name,
				ID
			FROM 	msdb.dbo.sysoperators ) O3
ON		O3.ID = J.notify_page_operator_id

PRINT 'AGENT JOBS POPULATED'

GO

/*
INSERT CURRENT JOB STEPS INTO AGENT_JOBSTEPS TABLE
*/


DECLARE 	@AGENT_JOB_ID INT
SET 		@AGENT_JOB_ID = 1

WHILE 		@AGENT_JOB_ID < = (SELECT COUNT(*) FROM Control.dbo.AGENT_JOBS)
BEGIN
-- JOBSTEPS
INSERT INTO 	Control.dbo.AGENT_JOBSTEPS(
		AgentJobID, 
		StepSequence, 
		StepName, 
		SubSystem, 
		Command, 
		CmdExecSuccessCode, 
		OnSuccessAction, 
		OnSuccessStepID, 
		OnFailAction, 
		OnFailStepID, 
		DatabaseName, 
		DatabaseUser, 
		RetryAttempts, 
		RetryInterval )

SELECT 		AJ.AgentJobID,
		step_id AS StepSequence, 
		step_name, 
		subsystem, 
		command, 
		cmdexec_success_code, 
		on_success_action, 
		on_success_step_id, 
		on_fail_action, 
		on_fail_step_id, 
		database_name, 
		database_user_name, 
		retry_attempts, 
		retry_interval

FROM 		msdb.dbo.sysjobsteps SJS

INNER JOIN	msdb.dbo.sysjobs SJ
ON 		SJS.job_id = SJ.job_id

INNER JOIN	Control.dbo.AGENT_JOBS AJ
ON		AJ.AgentJobName = SJ.Name

WHERE		AJ.AgentJobID = @AGENT_JOB_ID

-- INCREMENT
SET		@AGENT_JOB_ID = @AGENT_JOB_ID + 1
END

PRINT 		'AGENT JOB STEPS POPULATED'

/*
INSERT CURRENT SCHEDULES INTO AGENT_JOBSCHEDULE TABLE
*/


SET 		@AGENT_JOB_ID = 1


WHILE 		@AGENT_JOB_ID < = (SELECT COUNT(*) FROM Control.dbo.AGENT_JOBS)
BEGIN
INSERT INTO 	Control.dbo.AGENT_JOBSCHEDULES(
		AgentJobID, 
		ScheduleName, 
		IsEnabled, 
		FreqType, 
		FreqInterval, 
		FreqSubdayType, 
		FreqSubdayInterval, 
		FreqRelativeInterval, 
		FreqRecurrenceFactor, 
		ActiveStartDate, 
		ActiveEndDate, 
		ActiveStartTime, 
		ActiveEndTime )

SELECT		AJ.AgentJobID,
		SCH.name, 
		SCH.enabled, 
		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_end_time

FROM 		msdb.dbo.sysschedules SCH

INNER JOIN	msdb.dbo.sysjobschedules SJ_SCH
ON		SJ_SCH.schedule_id = SCH.schedule_id

INNER JOIN	msdb.dbo.sysjobs SJ
ON 		SJ_SCH.job_id = SJ.job_id

INNER JOIN	Control.dbo.AGENT_JOBS AJ
ON		AJ.AgentJobName = SJ.Name

WHERE		AJ.AgentJobID = @AGENT_JOB_ID

SET		@AGENT_JOB_ID = @AGENT_JOB_ID + 1
END

PRINT		'AGENT JOB SCHEDULES POPULATED'