SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

USE Control
GO

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

CREATE PROCEDURE ResetSQLAgentJob
@pi_AgentJobName VARCHAR(125)

AS

/*
-------------------------------------------------------------------------------
-- DATE: 	11/11/2005
-- AUTHOR: 	Frank Bazan
-- DESC:	Gets and sets job back to its default parameters.
--		* Check if meta data exists, if not exit proc
--		* Check if job failed on previous run, if so exit proc.
--		* Add job if it doesn't exist in sysjobs & add '(local)' 
--		  target server to make job runnable.
--		* Gather general meta data and update general job attributes.
--		* Delete all steps, gather metadata, then add jobsteps.
--		* Delete all schedules, gather metadata, then add schedules.
--		* Update server environment.
-------------------------------------------------------------------------------
-- HISTORY	DATE		AUTHOR		DESCRIPTION
-------------------------------------------------------------------------------
-- 1.0		11/11/2005	FRANK BAZAN	1ST VERSION
-- 2.0		22/02/2006	Frank Bazan	* Added extra column to #step_details
						to cater for the new proxy_id provided
						by the 2005 version of sp_help_job
						* Added 2 extra columns to #schedule_details
						to cater for schedule_uid and job_count
						provided by the 2005 version of sp_help_schedule

*/
-----------------------------------
-- CHECK FOR AGENT_JOB% METADATA --
-----------------------------------
IF NOT EXISTS(SELECT * FROM Control.dbo.AGENT_JOBS WHERE AgentJobName = @pi_AgentJobName)
BEGIN
	RAISERROR('ADD METADATA FOR JOB: "%s" TO Control.dbo.AGENT_JOB TABLES BEFORE RUNNING THIS PROCEDURE', 16, 1, @pi_AgentJobName)
	RETURN
END
----------------------------
-- Get Current Run Status --
----------------------------
-- Run status of 0 means job failed on last attempt
IF Control.dbo.fn_DetermineJobRunStatus(@pi_AgentJobName) = 0
BEGIN
	RAISERROR ('JOB: "%s" FAILED ON PREVIOUS RUN. CANNOT RESET UNTIL JOB OUTCOME SUCCESSFUL OR CANCELLED', 16, 1, @pi_AgentJobName)
	RETURN
END

-- IF ANY PART OF JOB FAILS ROLLBACK TRANSACTION
BEGIN TRANSACTION

-- Operating Variables
DECLARE @return_code INT
DECLARE @Count INT
DECLARE @This_Schedule_Name VARCHAR(128)
DECLARE @This_Schedule_ID INT

-- Get AgentJobID
DECLARE @AgentJobID INT
SET 	@AgentJobID = ( SELECT AgentJobID FROM AGENT_JOBS WHERE AgentJobName = @pi_AgentJobName )

-- General job variables
DECLARE @AgentJobName SYSNAME
DECLARE @IsEnabled TINYINT
DECLARE @AgentJobDescription VARCHAR(512)
DECLARE @StartStepID INT
DECLARE @CategoryName SYSNAME
DECLARE @OwnerLoginName SYSNAME
DECLARE @NotifyLevelEventlog INT
DECLARE @NotifyLevelEmail INT
DECLARE @NotifyLevelNetsend INT
DECLARE @NotifyLevelPage INT
DECLARE @NotifyEmailOperator SYSNAME
DECLARE @NotifyNetsendOperator SYSNAME
DECLARE @NotifyPageOperator SYSNAME
DECLARE @DeleteLevel INT
DECLARE @ServerName SYSNAME

-- JobStep variables
DECLARE @StepSequence INT
DECLARE @StepName SYSNAME
DECLARE @AgentSubSystem VARCHAR(40)
DECLARE @CommandLine VARCHAR(3200)
DECLARE @CmdExecSuccessCode INT
DECLARE @OnSuccessAction TINYINT
DECLARE @OnSuccessStepID INT
DECLARE @OnFailAction TINYINT
DECLARE @OnFailStepID INT
DECLARE @DatabaseName SYSNAME
DECLARE @DatabaseUser SYSNAME
DECLARE @RetryAttempts INT
DECLARE @RetryInterval INT

-- JobSchedule variables
DECLARE @ScheduleID INT
DECLARE @ScheduleName SYSNAME
DECLARE @ScheduleEnabled TINYINT
DECLARE @FreqType INT
DECLARE @FreqInterval INT
DECLARE @FreqSubdayType INT
DECLARE @FreqSubdayInterval INT
DECLARE @FreqRelativeInterval INT
DECLARE @FreqRecurrenceFactor INT
DECLARE @ActiveStartDate INT
DECLARE @ActiveEndDate INT
DECLARE @ActiveStartTime VARCHAR(6)
DECLARE @ActiveEndTime VARCHAR(6)


--------------------------------
-- GET GENERAL JOB PARAMETERS --
--------------------------------
SET @AgentJobName = @pi_AgentJobName
SET @IsEnabled = ( SELECT IsEnabled FROM AGENT_JOBS WHERE AgentJobID = @AgentJobID)
SET @AgentJobDescription = ( SELECT AgentJobDescription FROM AGENT_JOBS WHERE AgentJobID = @AgentJobID)
SET @StartStepID = ( SELECT StartStepID FROM AGENT_JOBS WHERE AgentJobID = @AgentJobID)
SET @CategoryName = ( SELECT CategoryName FROM AGENT_JOBS WHERE AgentJobID = @AgentJobID)
SET @OwnerLoginName = ( SELECT OwnerLoginName FROM AGENT_JOBS WHERE AgentJobID = @AgentJobID)
SET @NotifyLevelEventlog = ( SELECT NotifyLevelEventlog FROM AGENT_JOBS WHERE AgentJobID = @AgentJobID)
-- A quirk of sp_update_job doesn't allow you to update Notification level to zero if a value already exists.
-- in sysjobs. This part of the job therefore can only be set manually if no notifications are required.
SET @NotifyLevelEmail = NULLIF(( SELECT NotifyLevelEmail FROM AGENT_JOBS WHERE AgentJobID = @AgentJobID),0)
SET @NotifyLevelNetsend = NULLIF(( SELECT NotifyLevelNetsend FROM AGENT_JOBS WHERE AgentJobID = @AgentJobID),0)
SET @NotifyLevelPage = NULLIF(( SELECT NotifyLevelPage FROM AGENT_JOBS WHERE AgentJobID = @AgentJobID),0)
SET @NotifyEmailOperator = ( SELECT NotifyEmailOperatorName FROM AGENT_JOBS WHERE AgentJobID = @AgentJobID)
SET @NotifyNetsendOperator = ( SELECT NotifyNetsendOperatorName FROM AGENT_JOBS WHERE AgentJobID = @AgentJobID)
SET @NotifyPageOperator = ( SELECT NotifyPageOperatorName FROM AGENT_JOBS WHERE AgentJobID = @AgentJobID)
SET @DeleteLevel = ( SELECT DeleteLevel FROM AGENT_JOBS WHERE AgentJobID = @AgentJobID)
SET @ServerName = ( SELECT ServerName FROM AGENT_JOBS WHERE AgentJobID = @AgentJobID)

PRINT @AgentJobID
PRINT @AgentJobName
PRINT @IsEnabled
PRINT @AgentJobDescription
PRINT @StartStepID
PRINT @CategoryName
PRINT @OwnerLoginName
PRINT @NotifyLevelEventlog
PRINT @NotifyLevelEmail
PRINT @NotifyLevelNetsend
PRINT @NotifyLevelPage
PRINT @NotifyEmailOperator
PRINT @NotifyNetsendOperator
PRINT @NotifyPageOperator
PRINT @DeleteLevel
PRINT @ServerName

-------------------
-- ADD NEW JOB?? --
-------------------

IF NOT EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE name = @pi_AgentJobName)
BEGIN
	EXECUTE @return_code = msdb.dbo.sp_add_job
		@job_name = @AgentJobName

	IF (@@ERROR <> 0 OR @return_code <> 0) GOTO QuitAndRollback

	EXECUTE @return_code = msdb.dbo.sp_add_jobserver
		@job_name = @AgentJobName,
		@server_name = @ServerName

	IF (@@ERROR <> 0 OR @return_code <> 0) GOTO QuitAndRollback

PRINT	'SQL AGENT JOB "' + @pi_AgentJobName + '" DOESN''T EXIST... ADDED'
END



-----------------------------------
-- UPDATE GENERAL JOB ATTRIBUTES --
-----------------------------------

EXEC @return_code = msdb.dbo.sp_update_job
	@job_name = @AgentJobName, 
	@owner_login_name = @OwnerLoginName, 
	@description = @AgentJobDescription, 
	@category_name = @CategoryName, 
	@enabled = @IsEnabled, 
	@notify_level_email = @NotifyLevelEmail,
	@notify_level_page = @NotifyLevelPage,
	@notify_level_netsend = @NotifyLevelNetsend,
	@notify_level_eventlog = @NotifyLevelEventlog, 
	@delete_level = @DeleteLevel, 
	@notify_email_operator_name = @NotifyEmailOperator, 
	@notify_page_operator_name = @NotifyPageOperator, 
	@notify_netsend_operator_name = @NotifyNetsendOperator

	IF (@@ERROR <> 0 OR @return_code <> 0) GOTO QuitAndRollback

---------------------------------
-- REMOVE ALL JOBSTEPS PRESENT --
---------------------------------

-- Temp table to store step details
CREATE TABLE #Step_Detail(
	step_id INT,
	step_name VARCHAR(125),
	subsystem VARCHAR(125),
	command VARCHAR(1024),
	flags CHAR(1),
	cmdexec_success_code CHAR(1),
	on_success_action CHAR(1),
	on_success_step_id CHAR(1),
	on_fail_action CHAR(1),
	on_fail_step_id CHAR(1),
	server VARCHAR(125),
	database_name VARCHAR(125),
	database_user_name VARCHAR(125),
	retry_attempts INT,
	retry_interval INT,
	os_run_priority INT,
	output_file_name VARCHAR(1024),
	last_run_outcome CHAR(1),
	last_run_duration INT,
	last_run_retries INT,
	last_run_date VARCHAR(8),
	last_run_time VARCHAR(6),
	proxy_id INT
)

INSERT INTO 	#Step_Detail
EXECUTE 	msdb.dbo.sp_help_jobstep @job_name = @AgentJobName


-- Delete each job step_id from max to 1
WHILE EXISTS (SELECT * FROM #Step_Detail)
BEGIN
SET	@Count = (SELECT MAX(step_id) FROM #Step_Detail)
	EXEC @return_code = msdb.dbo.sp_delete_jobstep
			@job_name = @AgentJobName,
			@step_id = @Count
	
	IF (@@ERROR <> 0 or @return_code <> 0) GOTO QuitAndRollback

	DELETE FROM #Step_Detail WHERE step_id = @Count
END

-- Clean up
DROP TABLE #Step_Detail

---------------------------
-- ADD JOBSTEPS IN ORDER --
---------------------------

-- Store step info from agent_jobstep table
DECLARE 	@StepTable TABLE (
		AgentJobID int NOT NULL,
		StepSequence int NULL,
		StepName varchar (128) NOT NULL,
		SubSystem varchar (40) NOT NULL,
		Command varchar (3200) NOT NULL,
		CmdExecSuccessCode int NOT NULL,
		OnSuccessAction int NOT NULL,
		OnSuccessStepID int NOT NULL,
		OnFailAction int NOT NULL,
		OnFailStepID int NOT NULL,
		DatabaseName sysname NULL,
		DatabaseUser sysname NULL,
		RetryAttempts int NULL,
		RetryInterval int NULL)

INSERT INTO 	@StepTable
SELECT 		AgentJobID, 
		StepSequence, 
		StepName, 
		SubSystem, 
		Command, 
		CmdExecSuccessCode, 
		OnSuccessAction, 
		OnSuccessStepID, 
		OnFailAction, 
		OnFailStepID, 
		DatabaseName, 
		DatabaseUser, 
		RetryAttempts, 
		RetryInterval 

FROM 		Control.dbo.AGENT_JOBSTEPS

WHERE		AgentJobID = @AgentJobID

ORDER BY	StepSequence

SELECT 		* 
FROM 		@StepTable

-- Loop while table variable has records. Delete record one by one as the steps are added to job
WHILE EXISTS (SELECT * FROM @StepTable)
BEGIN

-- Number stored in @Count is the record we will work with. 
-- Use min() to add jobsteps from the first upwards.
SET @COUNT = (SELECT MIN(StepSequence) FROM @StepTable)

-- Assign variables for this step
SET @StepSequence = 	(SELECT StepSequence FROM @StepTable WHERE StepSequence = @Count) 
SET @StepName = 	(SELECT StepName FROM @StepTable WHERE StepSequence = @Count) 
SET @AgentSubSystem = 	(SELECT SubSystem FROM @StepTable WHERE StepSequence = @Count) 
SET @CommandLine = 	(SELECT Command FROM @StepTable WHERE StepSequence = @Count) 
SET @CmdExecSuccessCode=(SELECT CmdExecSuccessCode FROM @StepTable WHERE StepSequence = @Count) 
SET @OnSuccessAction = 	(SELECT OnSuccessAction FROM @StepTable WHERE StepSequence = @Count) 
SET @OnSuccessStepID = 	(SELECT OnSuccessStepID FROM @StepTable WHERE StepSequence = @Count) 
SET @OnFailAction = 	(SELECT OnFailAction FROM @StepTable WHERE StepSequence = @Count) 
SET @OnFailStepID = 	(SELECT OnFailStepID FROM @StepTable WHERE StepSequence = @Count) 
SET @DatabaseName = 	(SELECT DatabaseName FROM @StepTable WHERE StepSequence = @Count) 
SET @DatabaseUser = 	(SELECT DatabaseUser FROM @StepTable WHERE StepSequence = @Count) 
SET @RetryAttempts =	(SELECT RetryAttempts FROM @StepTable WHERE StepSequence = @Count) 
SET @RetryInterval  = 	(SELECT RetryInterval FROM @StepTable WHERE StepSequence = @Count)

-- Add step
EXEC @return_code = msdb.dbo.sp_add_jobstep
	@job_name = @AgentJobName,
	@step_name = @StepName, 
	@command = @CommandLine,
	@database_name = @DatabaseName, 
	@database_user_name = @DatabaseUser, 
	@subsystem = @AgentSubSystem,
	@cmdexec_success_code = @CmdExecSuccessCode, 
	@retry_attempts = @RetryAttempts, 
	@retry_interval = @RetryInterval, 
	@on_success_step_id = @OnSuccessStepID, 
	@on_success_action = @OnSuccessAction, 
	@on_fail_step_id = @OnFailStepID, 
	@on_fail_action = @OnFailAction

	IF (@@ERROR <> 0 OR @return_code <> 0) GOTO QuitAndRollback

PRINT 	'STEP ' + CAST(@COUNT AS VARCHAR(2)) + ' ' + @StepName + ' ADDED'
DELETE FROM @StepTable WHERE StepSequence = @Count
END

--------------------------------
-- UPDATE JOB STARTING STEP   --
-- AFTER JOBS HAVE BEEN ADDED --
--------------------------------

EXEC @return_code = msdb.dbo.sp_update_job 
	@job_name = @AgentJobName, 
	@start_step_id = @StartStepID

	IF (@@ERROR <> 0 OR @return_code <> 0) GOTO QuitAndRollback

--------------------------
-- DELETE JOB SCHEDULES --
--------------------------
CREATE TABLE #Schedule_Detail (
	schedule_id INT,
	schedule_name VARCHAR(125),
	enabled CHAR(1),
	freq_type INT,
	freq_interval INT,
	freq_subday_type INT,
	freq_subday_interval INT,
	freq_relative_interval INT,
	freq_recurrence_factor INT,
	active_start_date VARCHAR(8),
	active_end_date VARCHAR(8),
	active_start_time VARCHAR(6),
	active_end_time VARCHAR(6),
	date_created DATETIME,
	schedule_description VARCHAR(512),
	next_run_date VARCHAR(8),
	next_run_time VARCHAR(6),
	schedule_uid UNIQUEIDENTIFIER,
	job_count INT
)

INSERT INTO 	#Schedule_Detail
EXEC		msdb.dbo.sp_help_jobschedule @job_name = @AgentJobName


WHILE EXISTS (SELECT * FROM #Schedule_Detail)
BEGIN
	-- Get schedule name
	SET		@This_Schedule_Name = (	SELECT 	schedule_name 
						FROM 	#Schedule_Detail
						WHERE	schedule_id = (	SELECT 	MAX(schedule_id) 
									FROM #Schedule_Detail )
					)
	-- Delete that schedule from job.
	EXECUTE		@return_code = msdb.dbo.sp_delete_jobschedule 
				@job_name = @AgentJobName, 
				@name = @This_Schedule_Name

	IF (@@ERROR <> 0 OR @return_code <> 0) GOTO QuitAndRollback
	
	-- Delete that schedule from the temp table.
	DELETE FROM 	#Schedule_Detail
	WHERE		schedule_name = @This_Schedule_Name
	PRINT		'SCHEDULE "' + @This_Schedule_Name + '" DELETED.'
END

-- Cleanup
DROP TABLE #Schedule_Detail
 
---------------------------
-- ADD DEFAULT SCHEDULES --
---------------------------
-- Store schedule parameters in table variable.
DECLARE 	@JobSchedules TABLE(
		AgentJobID int,
		ScheduleID int,
		ScheduleName sysname,
		IsEnabled tinyint,
		FreqType int,
		FreqInterval int,
		FreqSubdayType int,
		FreqSubdayInterval int,
		FreqRelativeInterval int,
		FreqRecurrenceFactor int,
		ActiveStartDate int,
		ActiveEndDate int,
		ActiveStartTime varchar (6),
		ActiveEndTime varchar (6)
)
INSERT INTO 	@JobSchedules
SELECT 		AgentJobID, 
		ScheduleID, 
		ScheduleName, 
		IsEnabled, 
		FreqType, 
		FreqInterval, 
		FreqSubdayType, 
		FreqSubdayInterval, 
		FreqRelativeInterval, 
		FreqRecurrenceFactor, 
		ActiveStartDate, 
		ActiveEndDate, 
		ActiveStartTime, 
		ActiveEndTime 

FROM 		Control.dbo.AGENT_JOBSCHEDULES
WHERE		AgentJobID = @AgentJobID


WHILE EXISTS (SELECT * FROM @JobSchedules)
BEGIN

-- Start with lowest id so that they are added in the same order.
SET @This_Schedule_ID = (SELECT MIN(ScheduleID) FROM @JobSchedules)

-- Get schedule info
SET @ScheduleID = (SELECT ScheduleID FROM @JobSchedules WHERE ScheduleID = @This_Schedule_ID)
SET @ScheduleName  = (SELECT ScheduleName FROM @JobSchedules WHERE ScheduleID = @This_Schedule_ID)
SET @ScheduleEnabled  = (SELECT IsEnabled FROM @JobSchedules WHERE ScheduleID = @This_Schedule_ID)
SET @FreqType  = (SELECT FreqType FROM @JobSchedules WHERE ScheduleID = @This_Schedule_ID)
SET @FreqInterval  = (SELECT FreqInterval FROM @JobSchedules WHERE ScheduleID = @This_Schedule_ID)
SET @FreqSubdayType  = (SELECT FreqSubdayType FROM @JobSchedules WHERE ScheduleID = @This_Schedule_ID)
SET @FreqSubdayInterval  = (SELECT FreqSubdayInterval FROM @JobSchedules WHERE ScheduleID = @This_Schedule_ID)
SET @FreqRelativeInterval  = (SELECT FreqRelativeInterval FROM @JobSchedules WHERE ScheduleID = @This_Schedule_ID)
SET @FreqRecurrenceFactor  = (SELECT FreqRecurrenceFactor FROM @JobSchedules WHERE ScheduleID = @This_Schedule_ID)
SET @ActiveStartDate  = (SELECT ActiveStartDate FROM @JobSchedules WHERE ScheduleID = @This_Schedule_ID)
SET @ActiveEndDate  = (SELECT ActiveEndDate FROM @JobSchedules WHERE ScheduleID = @This_Schedule_ID)
SET @ActiveStartTime  = (SELECT ActiveStartTime FROM @JobSchedules WHERE ScheduleID = @This_Schedule_ID)
SET @ActiveEndTime  = (SELECT ActiveEndTime FROM @JobSchedules WHERE ScheduleID = @This_Schedule_ID)

-- Add schedule
EXECUTE @return_code = msdb.dbo.sp_add_jobschedule
	@job_name = @AgentJobName,
	@name = @ScheduleName,
	@enabled = @ScheduleEnabled,
	@freq_type = @FreqType,
	@active_start_date = @ActiveStartDate, 
	@active_start_time = @ActiveStartTime, 
	@freq_interval = @FreqInterval, 
	@freq_subday_type = @FreqSubdayType, 
	@freq_subday_interval = @FreqSubdayInterval, 
	@freq_relative_interval = @FreqRelativeInterval,
	@freq_recurrence_factor = @FreqRecurrenceFactor, 
	@active_end_date = @ActiveEndDate,
	@active_end_time = @ActiveEndTime

	IF (@@ERROR <> 0 OR @return_code <> 0) GOTO QuitAndRollback

PRINT 'SCHEDULE ' + @ScheduleName + ' ADDED'

-- Delete from table variable
DELETE FROM @JobSchedules WHERE ScheduleID = @This_Schedule_ID
END

COMMIT TRANSACTION

GOTO: Commited

QuitAndRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
RAISERROR ('ERROR RESETING SQL AGENT JOB "%s". CHECK VALID META DATA. PROCEDURE NOT RUN', 16, 1, @pi_AgentJobName)

Commited:
-- Blank label to skip roll back on successful completion of proc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO