Script to bulk change notification_method parameter for SQL server alerts

SQL Server Administration
1

Hi All,

Apologies if I am missing anything here.
As a new DBA I have identified that all the servers that I am responsible for across the estate use SQL Server Agent alerts.
These alerts are configured for particular errors such as "Error Number 832" and "Severity 020" for example.

Having taken over responsibility from someone else, I can see that alerts have been set up to send an email to a pre defined group (which is working correctly) as well as notify a pager - there are two options for this, in the options tab and response.
Having done some digging by scripting out the job I have already determined that the "notification_method" parameter for email is 1", by setting this it eliminates the use of pager.

I was wondering whether it is possible to script something that effectively takes all alerts into consideration regardless of name and sets "notification_method =1" which I can blanket run against multiple servers across my estate without the need to open a connection to each SQL server and untick through the UI in SSMS.

This is the script I am left with when editing an alert and unticking the pager options:

USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'Severity 025',
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@database_name=N'',
@notification_message=N'',
@event_description_keyword=N'',
@performance_condition=N'',
@wmi_namespace=N'',
@wmi_query=N'',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'Severity 025', @operator_name=N'SQLDBA', @notification_method = 1
GO

Many thanks in advance for your help!
Matt

2

hi

if i understand you correctly ..

you can use POWERShell ..
Or
there are also third party software ( Paid Premium type , Free Type )
which you can use ..
Or
https://docs.microsoft.com/en-us/sql/ssms/register-servers/execute-statements-against-multiple-servers-simultaneously?view=sql-server-ver15

3

DBAs typically generate a script, review it quickly, then run it. Here's a script generator for what you requested to do. If you click on "sql" in the results pane, you can copy the generated script to a new window for reviewing / parsing / executing.

IF OBJECT_ID('tempdb.dbo.#sql') IS NOT NULL
    DROP TABLE #sql
GO
CREATE TABLE #sql (
    id int IDENTITY(1, 1) NOT NULL,
    sql varchar(8000) NULL
    )

INSERT INTO #sql ( sql ) VALUES
('USE [msdb]')--,(' ')

INSERT INTO #sql ( sql ) 
SELECT 'EXEC msdb.dbo.sp_update_alert @name=N''' + name + ''', @include_event_description_in = 1;'
FROM msdb.dbo.sysalerts
WHERE include_event_description <> 1

--IF @@ROWCOUNT > 0
    --INSERT INTO #sql ( sql ) VALUES(' ')

INSERT INTO #sql ( sql )
SELECT 'EXEC msdb.dbo.sp_update_notification @alert_name=N''' + a.name + ''', ' +
    '@operator_name=N''' + ISNULL(o.name, CAST(n.operator_id AS varchar(10))) + ''', ' +
    '@notification_method = 1;'
FROM msdb.dbo.sysnotifications n
INNER JOIN msdb.dbo.sysalerts a ON a.id = n.alert_id 
LEFT OUTER JOIN msdb.dbo.sysoperators o ON o.id = n.operator_id
WHERE n.notification_method <> 1

SELECT *
FROM #sql
ORDER BY id;
4

Hi Scott,
This is really helpful, thanks a lot!

5

Will give this a look, appreciate the help!