By: Greg Robidoux | Updated: 2006-10-26 | Comments (4) | SQL Server Agent
Free MSSQLTips Webinar: Troubleshooting Performance Issues with SQL Server Agent
Problem
One of the things you can do with SQL Server 2000 is setup Operators to be notified when there is a job failure or an alert has been triggered that should notify your DBA team. The problem with setting up notifications for jobs is that you need to edit each and every job in order to turn on notifications. If you only have a few jobs this is not that much work, but if you have several jobs and have several servers making this change may take some time. In addition, if you are using Maintenance Plans there is not a way to automatically setup notifications for the jobs, so you would have to do this manually as well. Luckily there is a stored procedure that can help make this task easier.
Solution
As with everything you can do through Enterprise Manager there is also a T-SQL equivalent command or commands that can streamline the effort it takes to make mass updates. One of these stored procedures is "sp_update_job". This stored procedure is found in the msdb database and allows you to make changes to your job without having to use the GUI.
So lets take a look at an example of how you can do this across the board for all jobs much quicker then using Enterprise Manager. Before we begin you need to already have SQLMail setup in order to use this as well as have SQL Agent setup to use the mail account that you are using. There are other tips on the internet on how to do this, so we will not go through these steps.
Let's say we have an operator already setup called "SQLalerts" and we want to update all of our jobs to use this operator in case of a job failure. Again we could open each of our jobs and make the change manually, such as the following screen shows:
Like I mentioned earlier, this is easy to do for a few jobs, but if you have many jobs and\or servers it can be very time consuming.
Here is a sample T-SQL script that will create the commands for you. This script actually outputs the commands that can then be copied and pasted into QA and executed to make the updates. The script could also be changed to automatically issue the commands, but with this version you have the ability to look at the commands first before you execute them.
| use msdb GO DECLARE @operator varchar(50) |
When this gets run the following output is created:
| EXEC msdb.dbo.sp_update_job @job_ID = '589D2B60-EDBD-45B5-BDE6-4DD974D20D25' ,@notify_level_email = 2, @notify_email_operator_name = 'SQLalerts'
EXEC msdb.dbo.sp_update_job @job_ID = '6BE4306C-CC37-4D38-BC27-1B099601EF6A' ,@notify_level_email = 2, @notify_email_operator_name = 'SQLalerts' EXEC msdb.dbo.sp_update_job @job_ID = 'F7569D9A-641E-4130-90F4-535F0B11FC1E' ,@notify_level_email = 2, @notify_email_operator_name = 'SQLalerts' EXEC msdb.dbo.sp_update_job @job_ID = 'CD012AF2-BC96-4D9E-A03E-6ABB2F6048AF' ,@notify_level_email = 2, @notify_email_operator_name = 'SQLalerts' EXEC msdb.dbo.sp_update_job @job_ID = '451C94B4-8BA3-48AA-BB66-D184F0C25556' ,@notify_level_email = 2, @notify_email_operator_name = 'SQLalerts' EXEC msdb.dbo.sp_update_job @job_ID = '7EA95731-1E19-40F6-A5E3-325647DACDE9' ,@notify_level_email = 2, @notify_email_operator_name = 'SQLalerts' |
The results from the above can be copied and pasted into Query Analyzer to make the actual updates to your jobs. After the commands are executed all of my jobs now have "SQLalerts" as the operator to get notified when anyone of these jobs fail.
You can also set a different status for the notification besides job failure. Here is a list of the codes that would go into the @notify_level_email value.
| Value | Description |
| 0 | Never |
| 1 | On Success |
| 2 | On Failure |
| 3 | Always |
In addition to setting an email alert you have also have the ability to send alerts via netsend and pager. Here are the options to use, they also use the same values as in the table above:
- @notify_level_email (you must also set @notify_email_operator_name)
- @notify_level_netsend (you must also set @notify_netsend_operator_name)
- @notify_level_page (you must also set @notify_page_operator_name)
Next Steps
- Take the next step and change the query to automatically do the updates instead of just giving you the T-SQL code
- Look at ways to incorporate multiple servers into the process
- Setup SQLMail and setup operators, alerts and notifications to run on your server to notify you of job failures
Last Updated: 2006-10-26
About the author
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.
View all my tips