Include the notify operator option when scripting slerts using SSMS
- Hello, I am on SQL Server 2016: @@VERSION output:Microsoft SQL Server 2016 (RTM-CU2) (KB3182270) - 13.0.2164.0 (X64) Sep 9 2016 20:13:26 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)I have a question about the adding of the notification i.e. "Notify Operator" option on the Response tab of the Alert page in SSMS. Once I have created the alert and added the notification option to it, how can I generate the script of this alert along with the notification option so that I can deploy it to different servers. Because when I generate the script from SSMS, by right clicking an alert (Script Alert As --> CREATE To --> New query editor) I only the see the alert script getting generated, its missing the notification part i.e. (EXEC msdb.dbo.sp_add_notification @alert_name=N'', @operator_name=N'DBA', @notification_method = 1) is not generated.
This is weird. So if I have to script my existing alerts, I have to generate the alert script and after that manually add the msdb.dbo.sp_add_notification part to it every time?
Am I missing anything?Please help me understand.RegardsAmir - amirmir - Tuesday, March 14, 2017 4:03 PM
You understand it correctly. And no you aren't missing anything.
There is a script I just saw that will create the notifications for each alert - so that might be something your interested in. Or look at his and write your own, it's just using dynamic SQL to generate the sp_add_notification for each alert
Add Notification to all SQL AlertsSo you could just use the print option for the notifications and then have a script for alerts and a script for notifications.
Sue
- Sue_H - Tuesday, March 14, 2017 4:44 PM
Hello Sue, thank you for directing me to the link. That definitely helps in scripting the notifications. Much appreciate it.
-
When creating the Alert - at the top of the dialog box is a button to Script the changes. Before creating the alert click this button and it will script all of your changes including the notification step.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
- Jeffrey Williams 3188 - Wednesday, March 15, 2017 12:39 PM
Hello @Jeffrey Williams 3188 ,
Thank you for your post. Yes, that seems like the only way to script out the notification part when scripting the alert. Basically I was trying to figure out how to script already existing alerts including the notification part. Because when you use the generate the alert script by right clicking on the alert, it scripts out everything except the notification part.
Sort of annoying. I really hoped that the SSMS generated the script including the notification part since that option is already configured in the alert.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply