Content Management Server 2002 Service Pack 2 installation information

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'CreateBackgroundProcessingJob')
	BEGIN
		PRINT 'Dropping Procedure CreateBackgroundProcessingJob'
		DROP  Procedure  dbo.CreateBackgroundProcessingJob
	END

GO

PRINT 'Creating Procedure CreateBackgroundProcessingJob'
GO

CREATE Procedure dbo.CreateBackgroundProcessingJob
		(
		@databaseName sysname
		,@jobName sysname 
		)

AS

/**
 *	This stored procedure is used to create a job to perform the background
 *	processing. The DCA runs this stored procedure for every database when 
 *	the database is created or upgraded.
 *
 *	@param	@databaseName		Name of the database to create the job for
 *	@param	@jobName out		Name of the job created
 *
 *	@return	0 -		Success
 *			1 -		Failure
 *			100 -	The job already exists
 */

begin

	set nocount on
	
	declare @tranCount int
			,@jobId uniqueidentifier
			,@outputFileName nvarchar(250)
			,@jobStepCommand nvarchar(512)
			,@new_job_created tinyint
	
	--localizable strings
	declare @localized_string_InvalidInputParameter as nvarchar(128)
	set @localized_string_InvalidInputParameter = N'Invalid input parameter'
	
	declare @localized_string_JobDescription as nvarchar(128)
	set @localized_string_JobDescription = N'Purge expired information from MCMS tables'
	
	declare @localized_string_JobStep_ExpiredPages as nvarchar(128)
	set @localized_string_JobStep_ExpiredPages = N'Process expired pages'
	
	declare @localized_string_JobStep_DanglingPages as nvarchar(128)
	set @localized_string_JobStep_DanglingPages = N'Purge content for deleted pages'
	
	declare @localized_string_JobStep_GalleryBasedResources as nvarchar(128)
	set @localized_string_JobStep_GalleryBasedResources = N'Update gallery based resources'

	declare @localized_string_JobStep_DanglingBLOBs as nvarchar(128)
	set @localized_string_JobStep_DanglingBLOBs = N'Purge data for deleted resources'

	declare @localized_string_JobStep_DanglingACL as nvarchar(128)
	set @localized_string_JobStep_DanglingACL = N'Purge deleted rights groups from container ACLs'

	declare @localized_string_JobAlreadyExists as nvarchar(128)
	set @localized_string_JobAlreadyExists = N'The job already exists'
	
	--validate input
	if isnull(@databaseName, '') = '' or isnull(@jobName, '') = ''
	begin
		raiserror(@localized_string_InvalidInputParameter, 15, 1)
		return (1)	
	end
	
	--the job Name is the database Name prepended with 'BGP-'
	--set @jobName = cast('BGP-' + @databaseName as sysname)


	--check whether we have to create a category for BGP jobs
	IF not exists (select 1
					from msdb.dbo.syscategories
					where (category_class = 1 /*local*/)
					and (name = 'MCMS Background Processing'))
	begin
		exec msdb.dbo.sp_add_category @class = 'JOB'
									,@Type = 'LOCAL'
									,@Name = 'MCMS Background Processing'
		if @@error <> 0 return (1)	--failure
	end

	
	set @new_job_created = 0
		
	--start a new transaction if the caller has not started one
	set @tranCount = @@trancount
	if @tranCount > 0 
		save tran CreateBGPJob_tran
	else
		begin tran CreateBGPJob_tran
		

	--if the job already exists for this database, clear up all job steps
	if exists (select 1
				from msdb.dbo.sysjobs_view
				where name = @jobName
                and originating_server = lower(CONVERT(NVARCHAR(30), SERVERPROPERTY('ServerName'))))
    begin
		declare @stepcount int
		
		select @jobId = job_id 
		from msdb.dbo.sysjobs_view
		where name = @jobName
        and originating_server = lower(CONVERT(NVARCHAR(30), SERVERPROPERTY('ServerName')))
		
		select @stepcount = count(*)
		from msdb.dbo.sysjobsteps
		where job_id = @jobId
		
		while @stepcount > 0
		begin
			exec msdb.dbo.sp_delete_jobstep @job_name = @jobName, @step_id = @stepcount
			if @@error <> 0 return (1)	--failure	
			
			set @stepcount = @stepcount -1
		end
	end		
	else
	begin
		--create a new job
		exec msdb.dbo.sp_add_job @job_name = @jobName
								,@enabled = 1
								,@description = @localized_string_JobDescription
								,@category_name = 'MCMS Background Processing'
								,@job_id = @jobId output
		if @@error <> 0 goto errorhandler
		
		set @new_job_created = 1
	end

	set @outputFileName = '[SQLDIR]\JOBS\BGP-' + @databaseName + '-[STRTDT]-[STRTTM].log'
	
	--process expired pages
	exec msdb.dbo.sp_add_jobstep @job_id = @jobId
								,@step_name = @localized_string_JobStep_ExpiredPages					
								,@subsystem = 'TSQL'
								,@command = 'exec bgp_ProcessExpiredNodes'
								,@on_success_action = 3
								,@on_fail_action = 3
								,@database_name = @databaseName
								,@output_file_name = @outputFileName
								,@flags = 2
	if @@error <> 0 goto errorhandler

	--gallery based resources
	exec msdb.dbo.sp_add_jobstep @job_id = @jobId
								,@step_name = @localized_string_JobStep_DanglingPages					
								,@subsystem = 'TSQL'
								,@command = 'exec bgp_DeleteDanglingPages'
								,@on_success_action = 3
								,@on_fail_action = 3
								,@database_name = @databaseName
								,@output_file_name = @outputFileName
								,@flags = 2
	if @@error <> 0 goto errorhandler

	--gallery based resources
	exec msdb.dbo.sp_add_jobstep @job_id = @jobId
								,@step_name = @localized_string_JobStep_GalleryBasedResources					
								,@subsystem = 'TSQL'
								,@command = 'exec bgp_UpdateGalleryBasedResources'
								,@on_success_action = 3
								,@on_fail_action = 3
								,@database_name = @databaseName
								,@output_file_name = @outputFileName
								,@flags = 2
	if @@error <> 0 goto errorhandler

	--dangling ACLs
	exec msdb.dbo.sp_add_jobstep @job_id = @jobId
								,@step_name = @localized_string_JobStep_DanglingACL					
								,@subsystem = 'TSQL'
								,@command = 'exec bgp_DeleteDanglingACL'
								,@on_success_action = 3
								,@on_fail_action = 3
								,@database_name = @databaseName
								,@output_file_name = @outputFileName
								,@flags = 2
	if @@error <> 0 goto errorhandler

	--dangling BLOBs
	exec msdb.dbo.sp_add_jobstep @job_id = @jobId
								,@step_name = @localized_string_JobStep_DanglingBLOBs					
								,@subsystem = 'TSQL'
								,@command = 'exec bgp_DeleteDanglingBLOBs'
								,@on_success_action = 1
								,@on_fail_action = 2
								,@database_name = @databaseName
								,@output_file_name = @outputFileName
								,@flags = 2
	if @@error <> 0 goto errorhandler

	--set the second job step as the start step because, by default, we do not
	--process expired postings
	exec msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 2 
	
	if @new_job_created > 0
	begin
		--set the schedule	
		exec msdb.dbo.sp_add_jobschedule @job_id = @jobId
										,@Name = 'MCMS Background Processing Default'
										,@enabled = 1
										,@freq_type = 4
										,@freq_interval = 1
										,@active_start_time = 10000
		if @@error <> 0 goto errorhandler

		--notify the SQL Server Agent	
		exec msdb.dbo.sp_add_jobserver @job_id = @jobId
										,@server_name = N'(local)'	
		
		if @@error <> 0 goto errorhandler
	end
	
	--commit if we started our own transaction
	if @tranCount = 0
		commit tran	CreateBGPJob_tran
		
	return(0)
		
errorhandler:
	if @@trancount > 0
		rollback tran CreateBGPJob_tran
		
	return(1)

end
GO

GRANT EXEC ON dbo.CreateBackgroundProcessingJob TO CMSSystem
GO