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: