Hmmm, looks like this didn't copy/paste over correctly. Try this to create the job:
USE [msdb]
GO
/****** Object: Job [DBA_Reports - Laptop Fragmentation] Script Date: 09/12/2011 09:40:57 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [DBA Monitoring] Script Date: 09/12/2011 09:40:57 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA Monitoring' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA Monitoring'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA_Reports - Laptop Fragmentation',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Notifies laptop user that db maintenance will begin
Checks level of fragmentation on each laptop
Creates scripts to either redinex or reorganize
Send mail with list of laptops with fragmentation
Send mail with scripts to run
Notifies users that db maintenance is completed
Sends admin email notifying who was defragged today
Sends admin email notifying who has not defragged for at least 4 weeks
Send admin email on which indexs were defragged',
@category_name=N'DBA Monitoring',
@owner_login_name=N'sa',
@notify_email_operator_name=N'<add your info>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Clean Up Temp tables] Script Date: 09/12/2011 09:40:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Clean Up Temp tables',
@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'TRUNCATE TABLE FragmentedTables
TRUNCATE Table dbo.FragmentedTablesAfterDefragRun',
@database_name=N'DBA_Reports',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Call Fragmentation SPROC] Script Date: 09/12/2011 09:40:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Call Fragmentation SPROC',
@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 SelectAllServersFragmentedTables
',
@database_name=N'DBA_Reports',
@output_file_name=N'E:\Morning Reports\FragmentationOutputReport.txt',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Send email of who was defragged today] Script Date: 09/12/2011 09:40:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send email of who was defragged today',
@step_id=3,
@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 msdb.dbo.sp_start_job ''DBA_Reports - Report Server Fragmentaion Defragged Today''',
@database_name=N'DBA_Reports',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [send email for Laptops not Defragged for at least 4 weeks] Script Date: 09/12/2011 09:40:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'send email for Laptops not Defragged for at least 4 weeks',
@step_id=4,
@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 msdb.dbo.sp_start_job ''DBA_Reports - Report Laptops not Defragged for at least 4 weeks''',
@database_name=N'DBA_Reports',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step Script Date: 09/12/2011 09:40:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'email what was defragged and initial numbers',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC msdb.dbo.sp_start_job ''DBA_Reports - Report Servers Defragged Today''',
@database_name=N'DBA_Reports',
@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_jobschedule @job_id=@jobId, @name=N'schedule',
@enabled=1,
@freq_type=8,
@freq_interval=62,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20110523,
@active_end_date=99991231,
@active_start_time=131500,
@active_end_time=235959
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:
GO
Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook