Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Administration  Combined Errors Job

Author  Topic  newdba Starting Member 30 Posts Posted - 2011-06-14 : 11:12:23 I am creating a new job on the sql server agent. Please look at the script and tell me if it's okay. Since it's production the supervisor wants me to send him the script. I think I got everything except I need it to send an email out after its done with the results. please give feedback soon USE [msdb] GO BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 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'combined view Emails', @enabled=1, @notify_level_eventlog=0, @notify_level_email=3, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'MUSICARTS\dtrivedi', @notify_email_operator_name=N'Dhara Trivedi', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execute sp_Combined View_Emails', @step_id=1, @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'EXECUTE [PaywarePOS].[dbo].[Combined_Errors] ', @database_name=N'Combined_Errors', @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'Daily', @enabled=0, @freq_type=4, @freq_interval=0, @freq_subday_type=0, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20100723, @active_end_date=99991231, @active_start_time=124900, @active_end_time=235959, @schedule_uid=N'33f34a85-2a8d-49e1-a7f1-62537cd71538' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(pci-sql03)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC msdb.dbo.sp_send_dbmail @profile_name = 'msdb.dbo.sp_send_dbmail', @recipients = 'dtrivedi@musicarts.com', @query = 'SELECT * FROM [PAYwarePOS].[dbo].[COMBINED_ERRORS] ' , @subject = 'Combined View Errors Report', @attach_query_result_as_file = 1 ; COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO