Bringing Alerts and Operators together with Notifications

I have covered SQL Server Alerts (Alerts are good, arent they?) on this blog before and I more recently did a post regarding Notifications (Are your Jobs talking to you) and how they should be configured. Now we need to check that these things are linked up so that when an Alert condition is met that you get the appropriate Notifications sent to Operators.

Straight into the code we need and then a review of what it does …

So this code creates a temporary table variable to hold operator information briefly; runs the undocumented sp_msgetalertinfo stored procedure to get failsafe operator data from the registry on the server; queries sysalerts, sysnotifications and sysoperators to check that all alerts are set to notify operators

Where there is no operator being notified then the [Add Notification TSQL] column will have the TSQL to create a notification to the failsafe operator, if there is one.

As always, understand this code, especially the way that you can’t rely on undocumented stored procedures to be consistent or reliable, before you run this on any of your servers, especially those that hold information that is precious to you.