代码改变世界

SQL Server 创建数据库邮件

2013-10-09 12:21 听风吹雨 阅读(...) 评论(...) 编辑 收藏

一、 背景

  数据库发邮件通知数据库的运行状态(状态可以通过JOB形式获取)和信息,达到预警的效果。

 

二、 基础知识

  msdb系统数据库保存有关Job,Database Mail,Nodifyication等等之类信息的。MSSQL2005之后的邮件功能,使用了Services Broker进行了队列处理。然后使用外部进程,这个可以在配置成功Mail以后查看任务管理器中的进程。

  另外 sp_send_dbmail是手动发送邮件的存储过程,我们必须显示的赋值才能发送。

 

三、 图形界面方式创建数据库邮件

下面就以SSMS的图形界面介绍如何创建数据库邮件;

wps_clip_image-8248

(Figure1:数据库邮件)

wps_clip_image-16544

(Figure2:以步骤形式创建)

wps_clip_image-3040

(Figure3:创建配置文件名)

wps_clip_image-1195

(Figure4:添加Email帐号)

这里设置的Email信息是表示之后就以这个Email帐号发送邮件,这里的服务器名称填写的是smtp.126.com,端口为25,你需要通过下面的方式测试这个地址是否可以访问。如果不通有可能是你机器防火墙的问题。

wps_clip_image-10970

(Figure5:测试ping)

wps_clip_image-26121

(Figure6:测试telnet)

wps_clip_image-19701

(Figure7:配置文件与邮件对应关系)

这里你可以添加多个Email帐号,在失败的时候会使用下一个用户尝试发送。

wps_clip_image-10676

(Figure8:公共配置文件)

wps_clip_image-3616

(Figure9:系统尝试,不需要修改)

wps_clip_image-9040

(Figure10:成功界面)

wps_clip_image-17687

(Figure11:成功发送邮件)

 

四、 数据库邮件相关脚本

--脚本创建数据库邮件
--1.开启数据库邮件
EXEC sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'database mail xps',1
RECONFIGURE WITH OVERRIDE
GO

--2.创建邮件帐户信息
EXEC  msdb..sysmail_add_account_sp
      @ACCOUNT_NAME ='ETLErrorMailLog',--邮件帐户名称
      @EMAIL_ADDRESS ='******@126.com',--发件人邮件地址
      @DISPLAY_NAME ='系统管理员',--发件人姓名
      @REPLYTO_ADDRESS =NULL,
      @DESCRIPTION = NULL,
      @MAILSERVER_NAME = 'SMTP.126.COM',--邮件服务器地址
      @MAILSERVER_TYPE = 'SMTP',--邮件协议
      @PORT =25,--邮件服务器端口
      @USERNAME = '******@126.com',--用户名
      @PASSWORD = '******',--密码
      @USE_DEFAULT_CREDENTIALS =0,
      @ENABLE_SSL =0,
      @ACCOUNT_ID = NULL
GO

--3.数据库配置文件
IF EXISTS(SELECT name FROM msdb..sysmail_profile WHERE name=N'ETLErrorProfileLog')
BEGIN
    EXEC msdb..sysmail_delete_profile_sp
    @profile_name='ETLErrorProfileLog'
END

EXEC msdb..sysmail_add_profile_sp
    @profile_name = 'ETLErrorProfileLog',--profile名称
    @description = '数据库邮件配置文件',--profile描述
    @profile_id = null
GO

--4.用户和邮件配置文件相关联
EXEC msdb..sysmail_add_profileaccount_sp
    @profile_name = 'ETLErrorProfileLog',--profile名称
    @account_name = 'ETLErrorMailLog',--account名称
    @sequence_number = 1--account 在profile 中顺序
GO

--5.1发送简单文本的邮件
EXEC msdb..sp_send_dbmail
    @profile_name = 'ETLErrorProfileLog',--profile名称
    @recipients = '******@qq.com',--收件人
    @subject = 'Test title this is test ',--邮件标题
    @body = N'数据库邮件测试',--邮件内容
    @body_format = 'HTML'--邮件格式
GO

--5.2发送包含查询的邮件
EXEC msdb..sp_send_dbmail
    @profile_name = 'ETLErrorProfileLog',
    @recipients = '******@qq.com',
    @subject = '查询结果',
    @query = 'SELECT * FROM msdb.dbo.sysmail_faileditems'
GO

--5.2发送包含附件的邮件
EXEC msdb..sp_send_dbmail
    @profile_name = 'ETLErrorProfileLog',
    @recipients = '******@qq.com',
    @subject = '包含附件',
    @body = '有附件,请查收',
    @file_attachments = 'c:\a.txt'
GO

--5.3发送查询作为附件的邮件
EXEC msdb..sp_send_dbmail
    @profile_name = 'ETLErrorProfileLog',
    @recipients = '******@qq.com',
    @subject = '查询结果',
    @body = '查询结果在附件中',
    @query = 'SELECT * FROM msdb.dbo.sysmail_faileditems',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'a.txt'
GO

 

五、 参考文献

MSSQL数据库邮件系列二(SSMS和TSQL)

SQL Server:在 SQL Server 2005 中配置数据库邮件,发送邮件

SQL Server Reporting Services一步步实现邮件订阅

利用SQL SERVER2005发送邮件

SQL Server 监控系列 —— 一

祥解使用 SQL Server 2005/2008 发送数据库邮件

巧妙SQL Server设置 备份状态邮件通知

数据库邮件

数据库邮件消息处理对象

sp_send_dbmail (Transact-SQL)