Configure Email Alerts via T-SQL

You can run the following SQL script to ensure email alerts are configured for the LSCopy and LSRestore jobs:

-- Get the job_id values and stick them into a cursor:
DECLARE @jobID UNIQUEIDENTIFIER
DECLARE cur CURSOR LOCAL FOR
    SELECT [job_id|job_id] 
    FROM [msdb|msdb].[dbo|dbo].[sysjobs|sysjobs] 
    WHERE [name|name] LIKE 'LSCopy_%' OR [name|name] LIKE 'LSRestore_%'

-- Loop through the cursor:
OPEN cur
    FETCH NEXT FROM cur INTO @jobID
    WHILE @ @FETCH_STATUS = 0 
    BEGIN
        -- Call sp_update_job for each ID. Set email level to 2 
        -- (alert on fail) and the operator name:
        EXEC [msdb|msdb].[dbo|dbo].sp_update_job 
            @job_id=@jobID,
            @notify_level_email=2,
            @notify_email_operator_name=N'DBA-EmailGroup'

        FETCH NEXT FROM cur INTO @jobID
    END
CLOSE cur
DEALLOCATE cur

-- Get the job_id values and stick them into a cursor:

DECLARE @jobID UNIQUEIDENTIFIER

DECLARE cur CURSOR LOCAL FOR

SELECT [job_id|job_id]

FROM [msdb|msdb].[dbo|dbo].[sysjobs|sysjobs]

WHERE [name|name] LIKE 'LSCopy_%' OR [name|name] LIKE 'LSRestore_%'

-- Loop through the cursor:

OPEN cur

FETCH NEXT FROM cur INTO @jobID

WHILE @ @FETCH_STATUS = 0

BEGIN

-- Call sp_update_job for each ID. Set email level to 2

-- (alert on fail) and the operator name:

EXEC [msdb|msdb].[dbo|dbo].sp_update_job

@job_id=@jobID,

@notify_level_email=2,

@notify_email_operator_name=N'DBA-EmailGroup'

FETCH NEXT FROM cur INTO @jobID

END

CLOSE cur

DEALLOCATE cur

Views – 2607

LincolnBlog.net

…my observations in the world of SQL Server, PowerShell, Linux and PostgreSQL etc…

Configure Email Alerts via T-SQL

Leave a Reply Cancel reply