MOM 2005 Datawarehousing Catchup

USE [msdb]
GO
/****** Object:  Job [Datawarehousing Catchup]    
 ****** Created By:  Sean Gorman
 ****** Purpose:I wrote this script because our MOM Datawarehousing got so damn behind.
	Every time the windows Scheduled task tried to run to warehouse the data
	out of OnePoint, it would puke because the SQL log would fill up.  This
	allows you to start so many days out and it will increment it down by 5
	days and successively run the job over and over until it is completed
	(down to 5 days time)  It's an "On Demand" SQL job, not something that
	you would want to run all the time (obviously).  The job will groom and
	then re-partition the DB when it has caught up too which is nice.  You
	will want to disable the MOMX Partitioning and Grooming Job while this
	one goes.  You can use this to replace the windows scheduled task too and
	run it only in SQL Agent.  Makes life easier I think.


Script Date: 07/26/2007 16:47:00 ******/


BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 07/27/2007 08:39:38 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Datawarehousing Catchup', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'You will need to change the @startingdays variable to how far out you need to start.  This job will check every 5 minutes and try to kick off the next lowest 5-day increment of the DataWarehousingDTS task.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Run Job]    Script Date: 07/27/2007 08:39:39 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run Job', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'SET NOCOUNT ON
DECLARE @startingdays INT;
DECLARE @enddays INT;
DECLARE @inc_days INT;
DECLARE @onepointserver VARCHAR(255);
DECLARE @onepointdb VARCHAR(255);
DECLARE @scdwserver VARCHAR(255);
DECLARE @scdwdb VARCHAR(255);
--**************************************
--**************************************
--MAKE ADJUSTMENTS HERE for the Datawarehousing from OnePoint to SystemCenterReporting:

--How many days old should the data be on the first run of the DataWarehousing Job:
SET @startingdays = 365

--How many days old do you want your OnePoint database to keep in the end:
SET @enddays = 30

--When this job runs, how many days worth of data should the job chew on?  (Recommend no more than 5)
SET @inc_days = 3

--The following are your Server and Database Names for OnePoint and SystemCenterReporting
SET @onepointserver = ''MACPSQLMONP01''
SET @onepointdb = ''OnePoint''
SET @scdwserver = ''MACPSQLMONP01''
SET @scdwdb = ''SystemCenterReporting''
--**************************************
--**************************************
DECLARE @isrunning TINYINT;
DECLARE @sqlstmt VARCHAR (8000);
--
WHILE (SELECT @startingdays) > @enddays
	BEGIN
		CREATE TABLE #isrunning
			(
			result VARCHAR (8000)
			)

		INSERT INTO #isrunning
			EXEC xp_cmdshell ''tasklist /FI "IMAGENAME eq MOM.Datawarehousing*"''

		IF EXISTS (SELECT result from #isrunning where result LIKE ''MOM.Datawarehousing.DTS%'')
			BEGIN
			SET @isrunning = 1
			--SELECT ''YES IT IS RUNNING'' AS ANSWER
			END
		ELSE
			BEGIN
			SET @isrunning = 0
			END
		DROP TABLE #isrunning
		IF @isrunning = 1
			BEGIN
				WAITFOR DELAY  ''000:10:00''
			END
		IF @isrunning = 0
			BEGIN
				SET @sqlstmt = ''xp_cmdshell ''''"C:\Program Files\Microsoft System Center Reporting\Reporting\MOM.Datawarehousing.DTSPackageGenerator.exe" /silent /srcserver:'' + @onepointserver + '' /srcdb:'' + @onepointdb + '' /dwserver:'' + @scdwserver + '' /dwdb:'' + @scdwdb + '' /latency:'' + (CAST(@startingdays AS VARCHAR (20))) + char(39)
				EXEC (@sqlstmt)
				--PRINT @sqlstmt
				SET @startingdays = (@startingdays - @inc_days)
			END
		
	END', 
		@database_name=N'OnePoint', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Groom]    Script Date: 07/27/2007 08:39:39 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Groom', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC dbo.MOMXGrooming', 
		@database_name=N'OnePoint', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Partition]    Script Date: 07/27/2007 08:39:39 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Partition', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC dbo.MOMXPartitioning', 
		@database_name=N'OnePoint', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: