Presents your SQL SERVER E-NEWSLETTER for January 21, 2003 <-------------------------------------------> ASSIGN SQL OPERATORS TO E-MAIL NOTIFICATIONS FOR JOB FAILURES For SQL Server administration, it's important that SQL Agent jobs are being monitored, especially when the job fails. One monitoring method involves correlating job categories to SQL operators. You could have the same SQL operators assigned to several job categories. At the very least, assigning SQL operators to jobs for failure notification by e-mail or pager helps you maintain the system's reliability. The script below demonstrates the method of assigning SQL operators to e-mail notifications upon job failures based on job category. The script follows this outline: * A job is created with a particular job category. * A SQL operator is created. * A query is run that creates a script to update the job for e-mail notification upon job failure. * A query is run to list the jobs by job category and SQL operators are assigned for job failures. You will need to retrieve and execute the output of the query that creates a script to update the jobs for e-mail notification. This will complete the step of SQL operators being notified when certain jobs fail. Here is the script: use msdb go BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'DBA Maintenance') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'DBA Maintenance' -- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'Test Job Schedule Overlap') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- There is, so abort the script RAISERROR (N'Unable to import job ''Test Job Schedule Overlap'' since there is already a multi-server job with this name.', 16, 1) GOTO QuitWithRollback END ELSE -- Delete the [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N'Test Job Schedule Overlap' SELECT @JobID = NULL END BEGIN -- Add the job EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Test Job Schedule Overlap', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'DBA Maintenance', @enabled = 0, @notify_level_e-mail = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job steps EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'WaitForLongerThanOneMinute', @command = N'DECLARE @T INT SELECT @T = CONVERT(INT, SUBSTRING ( CONVERT(CHAR(3), DATEPART(MS,CURRENT_TIMESTAMP)) , 2,2)) WAITFOR DELAY @T', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job schedules EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'EveryMinute', @enabled = 1, @freq_type = 4, @active_start_date = 20021102, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the Target Servers EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO /* Script to: Add SQL Server operators: Operational DBA Update jobs when failed to notify SQL Server operators based on CATEGORY of job. */ use msdb go -- add operator IF (NOT EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = N'Operational DBA')) BEGIN PRINT '>>>>> SUCCEEDED Adding Operational DBA operator' EXECUTE msdb.dbo.sp_add_operator @name = N'Operational DBA', @enabled = 1, @e-mail_address = N'', @category_name = N'[Uncategorized]', @weekday_pager_start_time = 80000, @weekday_pager_end_time = 180000, @saturday_pager_start_time = 80000, @saturday_pager_end_time = 180000, @sunday_pager_start_time = 80000, @sunday_pager_end_time = 180000, @pager_days = 62 END ELSE BEGIN PRINT '>>>>> Operational DBA operator already exists, do not need to add.' END GO -- list operators EXEC msdb..sp_help_operator GO -- create script to assign operator to jobss by job category SELECT CASE cat.name WHEN 'DBA Maintenance' THEN -- OPERATIONAL DBA 'EXEC sp_update_job @job_name = ''' + j.name + ''', @notify_level_e-mail = 2 , @notify_e-mail_operator_name = ''Operational DBA''' ELSE '' END ,'--',substring(cat.name,1,20) category FROM sysjobs j JOIN syscategories cat on j.category_id = cat.category_id ORDER BY cat.name GO SELECT SUBSTRING(cat.name,1,20) category, SUBSTRING(j.name,1,30) job, j.notify_level_e-mail ntfy, SUBSTRING(op.name,1,20) op_name, COUNT(1), op.e-mail_address FROM sysjobs j JOIN syscategories cat ON j.category_id = cat.category_id LEFT JOIN sysoperators op ON j.notify_e-mail_operator_id = op.id GROUP BY cat.name, j.name, j.notify_level_e-mail, op.name, op.e-mail_address ORDER BY cat.name, j.name COMPUTE SUM(COUNT(1)) BY cat.name GO /* valid status values of a job that is being updated depending on this status @notify_level_e-mail Value Description (action) 0 Never 1 On success 2 On failure 3 Always */ ----------------------------------------