>>>> 背景:公司业务比较复杂,数据来源比较多,需要通过作业去同步数据,同步的频率都不同,导致经常数据没有及时同步,业务报障说数据在前台没法展示,所以通过邮件提醒来及时修正报错的作业。
由于公司数据库的实例多达四五十,单个实例的作业少则数十,多则数百,手动操作的话一个一个来太慢了,通过以下脚本进行批量操作就方便的多了。
一、创建邮件配置
以下脚本都是基于 [msdb] 库进行操作的,都需要指定数据库到 [msdb]
``` sql
USE [msdb]
GO
```
1.1、创建数据库邮件帐户
``` sql EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Alarm_Email', --要添加的帐户的名称
@description = 'DB_Job Failure Alarm', --帐户的说明(也是邮件发送方显示的名字)
@email_address = '[email protected]', --电子邮件地址(邮件发送方显示的地址,可填写具有辨识度的非真是邮箱,但必须为邮箱格式)
@replyto_address = '', --将响应来自此帐户的消息的地址发送到,默认值为 NULL
@display_name = '', --要用于来自此帐户的电子邮件的显示名称,默认值为 NULL。
@mailserver_name = '10.1.1.9' ; --SMTP 邮件服务器地址
GO
```
1.2、创建数据库邮件配置文件
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Alarm_Config', --新配置文件的名称。
@description = '' ; --新配置文件的说明(可选)。
GO
1.3、将帐户添加到配置文件
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Alarm_Config', --配置文件名字。
@account_name = 'Alarm_Email', --邮件账户名字。
@sequence_number = 1 ; --配置文件中帐户的序列号,序列号可以确定帐户在配置文件中的使用顺序。
GO
1.4、向 DBMailUsers 角色授予对配置文件的访问权限
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Alarm_Config', --关联的配置文件的名称,必须指定
@principal_name = 'public', --msdb 数据库中用于关联的数据库用户或角色的名称,必须指定
@is_default = 1 ;--指定此配置文件是否为主体的默认配置文件。 主体必须且只能有一个默认配置文件。
GO
现在邮件已经配置好了,可以执行以下代码进行测试
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Alarm_Config', --配置文件名称
@recipients = '[email protected]', --收件邮件的地址
@subject = 'Alarm Email Test Main Body', --邮件主题
@body = 'Alarm Email Test Main Body' --邮件正文内容
GO
*二、设置 SqlServer 代理*
2.1 启用警报系统
--启用邮件配置文件
EXEC msdb.dbo.sp_set_sqlagent_properties
@email_save_in_sent_folder=1,
@databasemail_profile=N'Alarm_Config' --配置文件名称
GO
--配置寻呼电子邮件内容
EXEC master.dbo.sp_MSsetalertinfo
@failsafeoperator=N'wmn', --启用防故障操作员
@notificationmethod=2, --1:电子邮件 2:寻呼程序
@pagertotemplate=N'[email protected]',--收件人邮箱地址 可设置多个,使用分号隔开 ;
@pagercctemplate=N'[email protected]',--抄送人邮箱地址 可设置多个,使用分号隔开 ;
@pagersendsubjectonly=0 --勾选在通知消息中包含电子邮件正文
GO
2.2、添加操作员
EXEC msdb.dbo.sp_update_operator
@name=N'sysop', --操作员姓名
@enabled=1, --启用
@weekday_pager_start_time=0, -- 工作日工作时间开始 0:00::00
@weekday_pager_end_time=235959, -- 工作日工作时间截止 23:59:59
@saturday_pager_start_time=0, -- 周六工作时间开始 0:00::00
@saturday_pager_end_time=235959, -- 周六工作时间截止 23:59:59
@sunday_pager_start_time=0, -- 周日工作时间开始 0:00::00
@sunday_pager_end_time=235959, -- 周日工作时间截止 23:59:59
@pager_days=127,
--以下两个地址选择使用哪个就填写哪个
@email_address=N'[email protected]', --邮件通知邮件地址
@pager_address=N'[email protected]' --寻呼电子邮件地址
GO
三、批量修改作业邮件通知
--3.1 定义操作员id
DECLARE @operator INT;
SET @operator = (SELECT id FROM msdb.dbo.sysoperators WHERE name = 'sysop')
--3.2通过电子邮件发送通知(接收方只能有一个)
UPDATE A SET
[notify_email_operator_id] = 0,--电子邮件通知方式(PS:只可以通知一个,及操作员邮箱地址)
[notify_page_operator_id] = @operator,--寻呼通知方式(PS:可抄送多个)
--以上两种方式都可以发送通知,根据自己使用场景来选择,选择方式及 赋值 @operator
[notify_level_page] = 2 --1 = 当作业成功时 2 = 当作业失败时 3 = 当作业完成时
FROM [msdb].[dbo].[sysjobs] AS A
--WHERE job_id = 'E15C1D9B-3E78-4C53-B8DB-E05A8DA7CDB3'
--根据需求来指定作业进行添加
大功告成!
标签:name,配置文件,--,msdb,teste,dbo,邮件 From: https://www.cnblogs.com/xq-542/p/17171934.html