USE Control
GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE Name = 'PopulateAgentJobTable' AND xtype = 'P')
DROP PROCEDURE PopulateAgentJobTable
GO

CREATE PROCEDURE PopulateAgentJobTable 
@pi_SQLJobName SYSNAME,
@UpdateInsert VARCHAR(6) = 'INSERT'

AS

/*

PROJECT:		BAU
AUTHOR:			FRANK BAZAN
DATE:			18/11/2005
DESCRIPTION:		SCRIPT INSERTS CURRENT JOB PARAMETERS FROM MSDB INTO
			THE CORE AGENT_JOB% TABLES
			* IF 'UPDATE' PASSED IN, DELETES ANY PREVIOUS METADATA
			* GATHERS MSDB SYSJOB DATA FOR THE PASSED IN JOB AND 
			  INSERT INTO THE AGENT_JOB% TABLES
------------------------------------------------------------------------------
HISTORY		DATE		AUTHOR		CHANGE
------------------------------------------------------------------------------
1.0		18/11/2005	FRANK BAZAN	1ST VERSION

2.0		22/02/2006	Frank Bazan	Change source table for schedule
						meta data. Now data is gathered
						from SysSchedules and associated
						to job with inner join to 
						sysjobschedules.

*/


BEGIN TRANSACTION

-- Delete all job, step and schedule records for the passed parameter
IF (@UpdateInsert = 'update')
BEGIN
DELETE FROM 	AGENT_JOBSCHEDULES
WHERE		AgentJobID = (	SELECT 	AgentJobID
				FROM 	AGENT_JOBS
				WHERE	AgentJobName = @pi_SQLJobName )
PRINT 'SCHEDULES DELETED'

DELETE FROM	AGENT_JOBSTEPS
WHERE		AgentJobID = (	SELECT 	AgentJobID
				FROM 	AGENT_JOBS
				WHERE	AgentJobName = @pi_SQLJobName )
PRINT 'STEPS DELETED'

DELETE FROM	AGENT_JOBS
WHERE		AgentJobName = @pi_SQLJobName

PRINT 'JOB DELETED'

END

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

WHERE		J.Name = @pi_SQLJobName

PRINT 'AGENT JOB POPULATED'

IF (@@ERROR <> 0) GOTO QuitAndRollback

-- 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.AgentJobName = @pi_SQLJobName

IF (@@ERROR <> 0) GOTO QuitAndRollback
PRINT 		'AGENT JOB STEPS POPULATED'

-- Job Schedules
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.AgentJobName = @pi_SQLJobName

IF (@@ERROR <> 0) GOTO QuitAndRollback
PRINT		'AGENT JOB SCHEDULES POPULATED'

COMMIT TRANSACTION

GOTO CommitTran

QuitAndRollback:
IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTION
RAISERROR('Could not add meta data to Agent Jobs tables for SQL Agent Job "%s"',16,1,@pi_SQLJobName)

CommitTran: