笔记49 SQL2005使用数据库邮件的步骤

笔记49 SQL2005使用数据库邮件的步骤

--SQL2005使用数据库邮件的步骤

注意：

在任何数据库中启用 SQL Server Service Broker 都需要数据库锁。如果在  msdb  中停用了 Service Broker，则若要启用数据库邮件，应首先停止 SQL Server 代理，以使 Service Broker 可以获取所需的锁。


sqlserver没有搭建SMTP服务器，所以在创建帐户的时候写的email地址是发件人地址
（1）
如果在帐户里写xxxxx@163.com


在存储过程里面写
EXEC [msdb].[dbo].[sp_send_dbmail]
    @profile_name = 'joe_profile2012-11-11' , -- sysname  --设置了配置文件也没有用monitorGPOSDB，内容还是跟没有设置配置文件一样
    @recipients = 'xxxx@163.com', -- varchar(max) 

那么登录163之后会看到邮件标题是  “我”
即sqlserver 发邮件到smtp.163.com   然后smtp.163.com 自己发翻俾自己
因为帐户里的邮箱地址是xxxxi@163.com  存储过程里的邮箱地址是xxxx@163.com

（2）
如果在帐户里写xxxx@qq.com

在存储过程里面写
EXEC [msdb].[dbo].[sp_send_dbmail]
    @profile_name = 'joe_profile2012-11-11' , -- sysname  --设置了配置文件也没有用monitorGPOSDB，内容还是跟没有设置配置文件一样
    @recipients = 'xxxxi@163.com', -- varchar(max) 



那么登录163之后会看到邮件标题是  “鱕虪”
即sqlserver 发邮件到smtp.qq.com   然后通过smtp.qq.com 发到xxx@163.com
因为帐户里的邮箱地址是xxxx@qq.com  存储过程里的邮箱地址是xxxx@163.com

net send 使用信使服务 在XP 或者win2003才有messager服务跟这个数据库邮件一点关系
都没有，在局域网内弹出一个对话框，里面有消息内容跟标题


--在以前的版本中，SQL Server通过Sql Mail来实现对电子邮件的收发等功能。
--在Sql Server2005中新增了数据库邮件功能，并且在今后的版本中将不再支持Sql Mail（我看sql2008还有这个选项在外围应用配置器里，是不是表示还可以使用sql mail）。
--原因在于SQL Mail使用外部电子邮件应用程序（例如Microsoft Outlook）中的扩展MAPI 客户端组件来发送和接收电子邮件。
--因此，若要使用SQL Mail，必须在运行SQL Server 的计算机上安装支持扩展MAPI 的电子邮件应用程序。
--而数据库邮件则是一种通过Microsoft SQL Server 2005 Database Engine 发送电子邮件的企业解决方案。
--通过使用数据库邮件，数据库应用程序可以向用户发送电子邮件。



--数据库邮件（Database Mail）设计为与SMTP 服务器配合使用，具有可靠性，灵活性，安全性，兼容性。考虑到Sql Server2005是基于.Net Framework 2.0，而在后者中新增了System.Net.Mail命名空间，相信数据库邮件也是利用了其中的一些功能。
--接下来就如何启用和配置数据库邮件进行介绍。

--出于安全性的考虑，默认地是不开启数据库邮件功能。
--启用数据库邮件功能有两种方式：
--方式一：使用SQL Server 外围应用配置器工具
--方式二：数据库邮件配置向导显式启用数据库邮件。

--方式一：
--sqlserver外围应用配置器-》功能的外围应用配置器-》数据库右键-》启用数据库邮件存储过程

--方式二：
--在SSMS里的对象资源管理器中的实例下，打开【管理】，然后在【数据库邮件】上右键后点击【配置数据库邮件】






点击【下一步】后出现【新建配置文件】：





然后点击【添加】添加帐户（账户名可以随便命名）：
这里的帐户不是sql帐户，是发送邮件的帐户，每个帐户对应一个邮箱地址，即是选择一个帐户就代表选择了一个邮箱，删除一个帐户就代表删除一个邮箱

SSL连接只能用于163邮箱，QQ邮箱不能使用SSL连接

在163邮箱里都显示“我”





EXEC msdb..sysmail_add_account_sp @account_name = 'joe'    -- 邮件帐户名称（SQL Server 使用）
    , @email_address = 'xxxx@qq.com'  -- 发件人邮件地址
    , @display_name = N'桦仔'                      -- 发件人姓名
    ,  @description = 'QQ邮箱'

点击【下一步】后管理配置文件安全性，将其设置为默认配置文件，一般创建一个配置文件就够了
可以创建多个配置文件，多个帐户，设置配置文件为专用还是公共






然后点击【下一步】完成配置，日志记录级别改为“详细”：


所有的配置选项都在这个界面


















测试的时候一定要写邮箱地址不能写账户名 例如：joe



使用警报来发送电子邮件，一般发生下图下面的错误的时候才会发送电子邮件
除了发送电子邮件之外也可以执行作业（作业本身已经预留了一些操作例如：数
据库维护，引擎优化顾问等作业，用户选择就可以了），也可以作业里自己写
TSQL， 不过前提都是在出现事件警报定义的错误的时候才触发





























Sql脚本：

--注意：附近路径必须要先建好附件不然会报附件文件C:\Users\Administrator\Desktop\Results.txt 无效
--注意：系统必须至少有一个配置文件否则会报错：未配置全局配置文件。请在@profile_name 参数中指定配置文件名
因为配置文件里已经指定了某些选项，所有觉得[sp_send_dbmail]存储过程的部分参数非常多余！！！

EXEC [msdb].[dbo].[sp_send_dbmail]
    @profile_name = 'joe_profile2012-11-11' ,
    @recipients = 'xxxxxx@163.com', -- varchar(max)    --收件人
    @copy_recipients = 'xxxxxx2@qq.com', -- varchar(max)   --抄送
    @subject = N'GPOSDB数据库systempara表数据', -- nvarchar(255)  标题
    @body = N'你好，GPOSDB数据库systempara表数据如下', -- nvarchar(max)    正文
    @body_format = 'html', -- varchar(20)    正文格式可选值：text  html
    @importance = 'HIGH', -- varchar(6)  重要等级邮件重要性必须为LOW、NORMAL 或HIGH
    @sensitivity = 'NORMAL', -- varchar(12)   敏感度邮件敏感度必须为NORMAL、PERSONAL、PRIVATE 或CONFIDENTIAL。
    @file_attachments = N'', -- nvarchar(max)  可以写上C:\Users\Administrator\Desktop\Results.txt
    @query = N'SELECT * from systempara', -- nvarchar(max)
    @execute_query_database = gposdb, -- sysname
    @attach_query_result_as_file = 0, -- bit
    @query_attachment_filename = N'C:\Users\Administrator\Desktop\Results.txt', -- nvarchar(260)
    @query_result_separator = ',', -- char(1)
    @append_query_error = 1, -- bit
    @query_result_no_padding = 0 --bit


使用DBCC CHECKDB([GPOSDB])检查GPOSDB数据库  结果不保存到附件的txt文件

EXEC [msdb].[dbo].[sp_send_dbmail]
    @profile_name = 'qw', -- sysname  --设置了配置文件也没有用monitorGPOSDB，内容还是跟没有设置配置文件一样
    @recipients = 'xxxxx@163.com', -- varchar(max)   接收人
    @subject = N'GPOSDB数据库运行状况', -- nvarchar(255)  标题
    @body = N'GPOSDB数据库运行状况', -- nvarchar(max)    正文
    @importance = 'HIGH', -- varchar(6)  重要等级邮件重要性必须为LOW、NORMAL 或HIGH
    @sensitivity = 'NORMAL', -- varchar(12)   敏感度邮件敏感度必须为NORMAL、PERSONAL、PRIVATE 或CONFIDENTIAL。
    @file_attachments = N'', -- nvarchar(max)  可以写上C:\Users\Administrator\Desktop\Results.txt
    @query = N'DBCC CHECKDB([GPOSDB])', -- nvarchar(max)
    @execute_query_database = gposdb, -- sysname
    @attach_query_result_as_file = 0, -- bit
    @query_attachment_filename = N'', -- nvarchar(260)
    @query_result_separator = ',', -- char(1)
    @append_query_error = 1, -- bit
    @query_result_no_padding = 0 --bit








-------------------------------------------------------------------------------------------------------------------------------

网易博客
数据库邮件是从 SQL Server 数据库引擎中发送电子邮件的企业解决方案。通过使用数据库邮件，数据库应用程序可以向用户发送电子邮件。邮件中可以包含查询结果，还可以包含来自网络中任何资源的文件。 无需 Microsoft Outlook 或扩展消息处理应用程序编程接口（扩展 MAPI）。数据库邮件使用标准的简单邮件传输协议 (SMTP) 发送邮件。无须在运行 SQL Server 的计算机上安装扩展 MAPI 客户端便可以使用数据库邮件。




对于真正的 DBA 来说，数据库邮件是必不可少的。 例如，数据库发生了警报（alert）， DBA 希望得到邮件通知，以便即时排除故障。 或者是监控数据库作业（SQL Server Job）的运行状况，当检查到失败的作业时， 就发送数据库邮件报告给 DBA。
在 SQL Server 2000 中 配置 “SQL Mail”，需要安装 Outlook，配置过程比较麻烦。 在 SQL Server 2005 中配置 “Database Mail” 就相对容易多了。 主要是理清思路。
SQL Server 并没有内置邮件服务器（Mail Server）， 它跟我们发送邮件一样，需要用户名和密码通过 SMTP（Simple Message Transfer Protocol） 去连接邮件服务器。我们想让 SQL Server 来发送邮件，首先要告诉它用户名称，密码， 服务器地址，网络传送协议，邮件服务器的端口。。。等信息。这是通过 SQL Server 系统 存储过程 sysmail_add_account_sp 来实现的。
exec sysmail_add_account_sp

这样，在 SQL Server 2005 中就添加了一个发送邮件的帐户。 道理上讲，有了这个邮件帐户，SQL Server 就可以发送邮件了。 如：

sp_send_dbmail @account_name = 'mail_account'

但是，SQL Server 考虑的更周全。试想：如果这个邮件帐户发生故障 （比如：用户密码过期，或者邮件服务器宕机）那岂不是发送不了邮件了？ 为了应对这种情况，SQL Server 2005 引入了 mail profile 这个东东。 一个 profile 中可以包含多个 account （邮件帐户），这样，SQL Server 发邮件的时候会依次尝试 profile 中的多个邮件帐户，如果发送成功，则退出， 否则，利用下一个邮件帐户发送邮件。其中，添加 profile 和 在 account 和 profile 建立映射是通过下面两个系统存储过程实现的：

sysmail_add_profile_sp
sysmail_add_profileaccount_sp

这时候，SQL Server 发送邮件，就采用下面的方法了：

sp_send_dbmail @profile_name = 'profile_name'

下面是具体的配置邮件步骤

在 sa 系统帐户下运行。

1.     启用 SQL Server 2005 邮件功能。
use master
go

exec sp_configure 'show advanced options',1
go

reconfigure
go

exec sp_configure 'Database mail XPs',1
go

reconfigure
go
--启动数据库邮件服务
EXEC [msdb].[dbo].[sysmail_start_sp]
GO


2在 SQL Server 2005 中添加邮件帐户（account）

EXEC msdb..sysmail_add_account_sp @account_name = 'joe'    -- 邮件帐户名称（SQL Server 使用）
    , @email_address = 'xxxx@qq.com'  -- 发件人邮件地址
    , @display_name = N'桦仔'                      -- 发件人姓名
    ,  @description = 'QQ邮箱'
    ,@mailserver_name = 'smtp.qq.com'           -- 邮件服务器地址
    , @mailserver_type = 'SMTP'                    -- 邮件协议（SQL 2005 只支持SMTP），而且区分大小写，只能填这个
    , @port = 25                        -- 邮件服务器端口
    , @username = 'xxxxx'  -- 用户名
    , @password = 'xxxxxx'               -- 密码
    , @use_default_credentials = 0, @enable_ssl = 0 --QQ邮箱不能使用SSL
    , @account_id = NULL




3在 SQL Server 2005 中添加 profile

EXEC msdb..sysmail_add_profile_sp @profile_name = '监控GPOSDB 2012-11-11'      -- profile 名称
    , @description = '监控GPOSDB 2012-11-11' -- profile 描述
    , @profile_id = NULL



4. 在 SQL Server 2005 中映射 account 和 profile

EXEC msdb..sysmail_add_profileaccount_sp @profile_name = '监控GPOSDB 2012-11-11' -- profile 名称
    , @account_name = 'joe'     -- account 名称
    , @sequence_number = 1             -- account 在profile 中顺序



5. 利用 SQL Server 2005 Database Mail 功能发送邮件。

EXEC [msdb].[dbo].[sp_send_dbmail]
    @profile_name = 'joe_profile2012-11-11' ,
    @recipients = 'xxxxx@163.com', -- varchar(max)    --收件人
    @copy_recipients = 'xxxxx@qq.com', -- varchar(max)   --抄送
    @subject = N'GPOSDB数据库systempara表数据', -- nvarchar(255)  标题
    @body = N'你好，GPOSDB数据库systempara表数据如下', -- nvarchar(max)    正文
    @body_format = 'html', -- varchar(20)    正文格式可选值：text  html
    @importance = 'HIGH', -- varchar(6)  重要等级邮件重要性必须为LOW、NORMAL 或HIGH
    @sensitivity = 'NORMAL', -- varchar(12)   敏感度邮件敏感度必须为NORMAL、PERSONAL、PRIVATE 或CONFIDENTIAL。
    @file_attachments = N'', -- nvarchar(max)  可以写上C:\Users\Administrator\Desktop\Results.txt
    @query = N'SELECT * from systempara', -- nvarchar(max)
    @execute_query_database = gposdb, -- sysname
    @attach_query_result_as_file = 0, -- bit
    @query_attachment_filename = N'C:\Users\Administrator\Desktop\Results.txt', -- nvarchar(260)
    @query_result_separator = ',', -- char(1)
    @append_query_error = 1, -- bit
@query_result_no_padding = 0 –bit

---------------监控GPOSDB-------------------------------------------
EXEC [msdb].[dbo].[sp_send_dbmail]
    @profile_name = '监控GPOSDB 2012-11-11'   ,
    @recipients = 'xxxxx@163.com', -- varchar(max)    --收件人
    @subject = N'GPOSDB数据库运行状况', -- nvarchar(255)  标题
    @body_format = 'text', -- varchar(20)    正文格式可选值：text  html
    @importance = 'HIGH', -- varchar(6)  重要等级邮件重要性必须为LOW、NORMAL 或HIGH
    @sensitivity = 'NORMAL', -- varchar(12)   敏感度邮件敏感度必须为NORMAL、PERSONAL、PRIVATE 或CONFIDENTIAL。
    @query = N'DBCC CHECKDB([GPOSDB])', -- nvarchar(max)
    @execute_query_database = gposdb, -- sysname
    @query_result_separator = ',', -- char(1)
    @append_query_error = 1, -- bit
    @query_result_no_padding = 0 --bit
   
   

创建好之后放入维护计划！！！！！！！




6. 查看邮件发送情况：

use msdb
go
select * from sysmail_allitems
select * from sysmail_mailitems
select * from sysmail_event_log



如果不是以 sa 帐户发送邮件，则可能会出现错误：
Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

这是因为，当前 SQL Server 登陆帐户（login），在 msdb 数据库中没有发送数据库邮件的权限， 需要加入 msdb 数据库用户，并通过加入 sp_addrolemember 角色赋予权限。假设该SQL Server 登陆帐户 名字为 “dba”

use msdb
go
create user dba for login dba
go
exec dbo.sp_addrolemember @rolename   = 'DatabaseMailUserRole',
                          @membername = 'dba'
Go





此时，再次发送数据库邮件，仍可能有错误：
Msg 14607, Level 16, State 1, Procedure sp_send_dbmail, Line 119
profile name is not valid
虽然，数据库用户 “dba” 已经在 msdb 中拥有发送邮件的权限了， 但这还不够，他还需要有使用 profile：“dba_profile” 的权限


USE msdb
go

EXEC sysmail_add_principalprofile_sp @principal_name = 'dba',
    @profile_name = 'joe_profile2012-11-11', @is_default = 1


从上面的参数 @is_default=1 可以看出，一个数据库用户可以在多个 mail profile 拥有发送权限。



只有 sysadmin 固定服务器角色的成员才能使用“发送测试电子邮件”对话框。如果用户不是 sysadmin 固定服务器角色的成员，可以使用 sp_send_dbmail 过程来测试数据库邮件。
JOE\Administrator是sysadmin角色