1 --SQL2005使用数据库邮件的步骤
2
3 注意：
4
5 在任何数据库中启用 SQL Server Service Broker 都需要数据库锁。如果在 msdb 中停用了 Service Broker，则若要启用数据库邮件，应首先停止 SQL Server 代理，以使 Service Broker 可以获取所需的锁。
6
7
8 sqlserver没有搭建SMTP服务器，所以在创建帐户的时候写的email地址是发件人地址
9 （1）
10 如果在帐户里写xxxxx@163.com
11
12
13 在存储过程里面写
14 EXEC [msdb].[dbo].[sp_send_dbmail]
15 @profile_name = 'joe_profile2012-11-11' , -- sysname --设置了配置文件也没有用monitorGPOSDB，内容还是跟没有设置配置文件一样
16 @recipients = 'xxxx@163.com', -- varchar(max)
17
18 那么登录163之后会看到邮件标题是 “我”
19 即sqlserver 发邮件到smtp.163.com 然后smtp.163.com 自己发翻俾自己
20 因为帐户里的邮箱地址是xxxxi@163.com 存储过程里的邮箱地址是xxxx@163.com
21
22 （2）
23 如果在帐户里写xxxx@qq.com
24
25 在存储过程里面写
26 EXEC [msdb].[dbo].[sp_send_dbmail]
27 @profile_name = 'joe_profile2012-11-11' , -- sysname --设置了配置文件也没有用monitorGPOSDB，内容还是跟没有设置配置文件一样
28 @recipients = 'xxxxi@163.com', -- varchar(max)
29
30
31
32 那么登录163之后会看到邮件标题是 “鱕虪”
33 即sqlserver 发邮件到smtp.qq.com 然后通过smtp.qq.com 发到xxx@163.com
34 因为帐户里的邮箱地址是xxxx@qq.com 存储过程里的邮箱地址是xxxx@163.com
35
36 net send 使用信使服务 在XP 或者win2003才有messager服务跟这个数据库邮件一点关系
37 都没有，在局域网内弹出一个对话框，里面有消息内容跟标题
38
39
40 --在以前的版本中，SQL Server通过Sql Mail来实现对电子邮件的收发等功能。
41 --在Sql Server2005中新增了数据库邮件功能，并且在今后的版本中将不再支持Sql Mail（我看sql2008还有这个选项在外围应用配置器里，是不是表示还可以使用sql mail）。
42 --原因在于SQL Mail使用外部电子邮件应用程序（例如Microsoft Outlook）中的扩展MAPI 客户端组件来发送和接收电子邮件。
43 --因此，若要使用SQL Mail，必须在运行SQL Server 的计算机上安装支持扩展MAPI 的电子邮件应用程序。
44 --而数据库邮件则是一种通过Microsoft SQL Server 2005 Database Engine 发送电子邮件的企业解决方案。
45 --通过使用数据库邮件，数据库应用程序可以向用户发送电子邮件。
46
47
48
49 --数据库邮件（Database Mail）设计为与SMTP 服务器配合使用，具有可靠性，灵活性，安全性，兼容性。考虑到Sql Server2005是基于.Net Framework 2.0，而在后者中新增了System.Net.Mail命名空间，相信数据库邮件也是利用了其中的一些功能。
50 --接下来就如何启用和配置数据库邮件进行介绍。
51
52 --出于安全性的考虑，默认地是不开启数据库邮件功能。
53 --启用数据库邮件功能有两种方式：
54 --方式一：使用SQL Server 外围应用配置器工具
55 --方式二：数据库邮件配置向导显式启用数据库邮件。
56
57 --方式一：
58 --sqlserver外围应用配置器-》功能的外围应用配置器-》数据库右键-》启用数据库邮件存储过程
59
60 --方式二：
61 --在SSMS里的对象资源管理器中的实例下，打开【管理】，然后在【数据库邮件】上右键后点击【配置数据库邮件】
62
63
64
65
66
67
68 点击【下一步】后出现【新建配置文件】：
69
70
71
72
73
74 然后点击【添加】添加帐户（账户名可以随便命名）：
75 这里的帐户不是sql帐户，是发送邮件的帐户，每个帐户对应一个邮箱地址，即是选择一个帐户就代表选择了一个邮箱，删除一个帐户就代表删除一个邮箱
76
77 SSL连接只能用于163邮箱，QQ邮箱不能使用SSL连接
78
79 在163邮箱里都显示“我”
80
81
82
83
84
85 EXEC msdb..sysmail_add_account_sp @account_name = 'joe' -- 邮件帐户名称（SQL Server 使用）
86 , @email_address = 'xxxx@qq.com' -- 发件人邮件地址
87 , @display_name = N'桦仔' -- 发件人姓名
88 , @description = 'QQ邮箱'
89
90 点击【下一步】后管理配置文件安全性，将其设置为默认配置文件，一般创建一个配置文件就够了
91 可以创建多个配置文件，多个帐户，设置配置文件为专用还是公共
92
93
94
95
96
97
98 然后点击【下一步】完成配置，日志记录级别改为“详细”：
99
100
101 所有的配置选项都在这个界面
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120 测试的时候一定要写邮箱地址不能写账户名 例如：joe
121
122
123
124 使用警报来发送电子邮件，一般发生下图下面的错误的时候才会发送电子邮件
125 除了发送电子邮件之外也可以执行作业（作业本身已经预留了一些操作例如：数
126 据库维护，引擎优化顾问等作业，用户选择就可以了），也可以作业里自己写
127 TSQL， 不过前提都是在出现事件警报定义的错误的时候才触发
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157 Sql脚本：
158
159 --注意：附近路径必须要先建好附件不然会报附件文件C:\Users\Administrator\Desktop\Results.txt 无效
160 --注意：系统必须至少有一个配置文件否则会报错：未配置全局配置文件。请在@profile_name 参数中指定配置文件名
161 因为配置文件里已经指定了某些选项，所有觉得[sp_send_dbmail]存储过程的部分参数非常多余！！！
162
163 EXEC [msdb].[dbo].[sp_send_dbmail]
164 @profile_name = 'joe_profile2012-11-11' ,
165 @recipients = 'xxxxxx@163.com', -- varchar(max) --收件人
166 @copy_recipients = 'xxxxxx2@qq.com', -- varchar(max) --抄送
167 @subject = N'GPOSDB数据库systempara表数据', -- nvarchar(255) 标题
168 @body = N'你好，GPOSDB数据库systempara表数据如下', -- nvarchar(max) 正文
169 @body_format = 'html', -- varchar(20) 正文格式可选值：text html
170 @importance = 'HIGH', -- varchar(6) 重要等级邮件重要性必须为LOW、NORMAL 或HIGH
171 @sensitivity = 'NORMAL', -- varchar(12) 敏感度邮件敏感度必须为NORMAL、PERSONAL、PRIVATE 或CONFIDENTIAL。
172 @file_attachments = N'', -- nvarchar(max) 可以写上C:\Users\Administrator\Desktop\Results.txt
173 @query = N'SELECT * from systempara', -- nvarchar(max)
174 @execute_query_database = gposdb, -- sysname
175 @attach_query_result_as_file = 0, -- bit
176 @query_attachment_filename = N'C:\Users\Administrator\Desktop\Results.txt', -- nvarchar(260)
177 @query_result_separator = ',', -- char(1)
178 @append_query_error = 1, -- bit
179 @query_result_no_padding = 0 --bit
180
181
182 使用DBCC CHECKDB([GPOSDB])检查GPOSDB数据库 结果不保存到附件的txt文件
183
184 EXEC [msdb].[dbo].[sp_send_dbmail]
185 @profile_name = 'qw', -- sysname --设置了配置文件也没有用monitorGPOSDB，内容还是跟没有设置配置文件一样
186 @recipients = 'xxxxx@163.com', -- varchar(max) 接收人
187 @subject = N'GPOSDB数据库运行状况', -- nvarchar(255) 标题
188 @body = N'GPOSDB数据库运行状况', -- nvarchar(max) 正文
189 @importance = 'HIGH', -- varchar(6) 重要等级邮件重要性必须为LOW、NORMAL 或HIGH
190 @sensitivity = 'NORMAL', -- varchar(12) 敏感度邮件敏感度必须为NORMAL、PERSONAL、PRIVATE 或CONFIDENTIAL。
191 @file_attachments = N'', -- nvarchar(max) 可以写上C:\Users\Administrator\Desktop\Results.txt
192 @query = N'DBCC CHECKDB([GPOSDB])', -- nvarchar(max)
193 @execute_query_database = gposdb, -- sysname
194 @attach_query_result_as_file = 0, -- bit
195 @query_attachment_filename = N'', -- nvarchar(260)
196 @query_result_separator = ',', -- char(1)
197 @append_query_error = 1, -- bit
198 @query_result_no_padding = 0 --bit
199
200
201
202
203
204
205
206
207 -------------------------------------------------------------------------------------------------------------------------------
208
209 网易博客
210 数据库邮件是从 SQL Server 数据库引擎中发送电子邮件的企业解决方案。通过使用数据库邮件，数据库应用程序可以向用户发送电子邮件。邮件中可以包含查询结果，还可以包含来自网络中任何资源的文件。 无需 Microsoft Outlook 或扩展消息处理应用程序编程接口（扩展 MAPI）。数据库邮件使用标准的简单邮件传输协议 (SMTP) 发送邮件。无须在运行 SQL Server 的计算机上安装扩展 MAPI 客户端便可以使用数据库邮件。
211
212
213
214
215 对于真正的 DBA 来说，数据库邮件是必不可少的。 例如，数据库发生了警报（alert）， DBA 希望得到邮件通知，以便即时排除故障。 或者是监控数据库作业（SQL Server Job）的运行状况，当检查到失败的作业时， 就发送数据库邮件报告给 DBA。
216 在 SQL Server 2000 中 配置 “SQL Mail”，需要安装 Outlook，配置过程比较麻烦。 在 SQL Server 2005 中配置 “Database Mail” 就相对容易多了。 主要是理清思路。
217 SQL Server 并没有内置邮件服务器（Mail Server）， 它跟我们发送邮件一样，需要用户名和密码通过 SMTP（Simple Message Transfer Protocol） 去连接邮件服务器。我们想让 SQL Server 来发送邮件，首先要告诉它用户名称，密码， 服务器地址，网络传送协议，邮件服务器的端口。。。等信息。这是通过 SQL Server 系统 存储过程 sysmail_add_account_sp 来实现的。
218 exec sysmail_add_account_sp
219
220 这样，在 SQL Server 2005 中就添加了一个发送邮件的帐户。 道理上讲，有了这个邮件帐户，SQL Server 就可以发送邮件了。 如：
221
222 sp_send_dbmail @account_name = 'mail_account'
223
224 但是，SQL Server 考虑的更周全。试想：如果这个邮件帐户发生故障 （比如：用户密码过期，或者邮件服务器宕机）那岂不是发送不了邮件了？ 为了应对这种情况，SQL Server 2005 引入了 mail profile 这个东东。 一个 profile 中可以包含多个 account （邮件帐户），这样，SQL Server 发邮件的时候会依次尝试 profile 中的多个邮件帐户，如果发送成功，则退出， 否则，利用下一个邮件帐户发送邮件。其中，添加 profile 和 在 account 和 profile 建立映射是通过下面两个系统存储过程实现的：
225
226 sysmail_add_profile_sp
227 sysmail_add_profileaccount_sp
228
229 这时候，SQL Server 发送邮件，就采用下面的方法了：
230
231 sp_send_dbmail @profile_name = 'profile_name'
232
233 下面是具体的配置邮件步骤
234
235 在 sa 系统帐户下运行。
236
237 1. 启用 SQL Server 2005 邮件功能。
238 use master
239 go
240
241 exec sp_configure 'show advanced options',1
242 go
243
244 reconfigure
245 go
246
247 exec sp_configure 'Database mail XPs',1
248 go
249
250 reconfigure
251 go
252 --启动数据库邮件服务
253 EXEC [msdb].[dbo].[sysmail_start_sp]
254 GO
255
256
257 2在 SQL Server 2005 中添加邮件帐户（account）
258
259 EXEC msdb..sysmail_add_account_sp @account_name = 'joe' -- 邮件帐户名称（SQL Server 使用）
260 , @email_address = 'xxxx@qq.com' -- 发件人邮件地址
261 , @display_name = N'桦仔' -- 发件人姓名
262 , @description = 'QQ邮箱'
263 ,@mailserver_name = 'smtp.qq.com' -- 邮件服务器地址
264 , @mailserver_type = 'SMTP' -- 邮件协议（SQL 2005 只支持SMTP），而且区分大小写，只能填这个
265 , @port = 25 -- 邮件服务器端口
266 , @username = 'xxxxx' -- 用户名
267 , @password = 'xxxxxx' -- 密码
268 , @use_default_credentials = 0, @enable_ssl = 0 --QQ邮箱不能使用SSL
269 , @account_id = NULL
270
271
272
273
274 3在 SQL Server 2005 中添加 profile
275
276 EXEC msdb..sysmail_add_profile_sp @profile_name = '监控GPOSDB 2012-11-11' -- profile 名称
277 , @description = '监控GPOSDB 2012-11-11' -- profile 描述
278 , @profile_id = NULL
279
280
281
282 4. 在 SQL Server 2005 中映射 account 和 profile
283
284 EXEC msdb..sysmail_add_profileaccount_sp @profile_name = '监控GPOSDB 2012-11-11' -- profile 名称
285 , @account_name = 'joe' -- account 名称
286 , @sequence_number = 1 -- account 在profile 中顺序
287
288
289
290 5. 利用 SQL Server 2005 Database Mail 功能发送邮件。
291
292 EXEC [msdb].[dbo].[sp_send_dbmail]
293 @profile_name = 'joe_profile2012-11-11' ,
294 @recipients = 'xxxxx@163.com', -- varchar(max) --收件人
295 @copy_recipients = 'xxxxx@qq.com', -- varchar(max) --抄送
296 @subject = N'GPOSDB数据库systempara表数据', -- nvarchar(255) 标题
297 @body = N'你好，GPOSDB数据库systempara表数据如下', -- nvarchar(max) 正文
298 @body_format = 'html', -- varchar(20) 正文格式可选值：text html
299 @importance = 'HIGH', -- varchar(6) 重要等级邮件重要性必须为LOW、NORMAL 或HIGH
300 @sensitivity = 'NORMAL', -- varchar(12) 敏感度邮件敏感度必须为NORMAL、PERSONAL、PRIVATE 或CONFIDENTIAL。
301 @file_attachments = N'', -- nvarchar(max) 可以写上C:\Users\Administrator\Desktop\Results.txt
302 @query = N'SELECT * from systempara', -- nvarchar(max)
303 @execute_query_database = gposdb, -- sysname
304 @attach_query_result_as_file = 0, -- bit
305 @query_attachment_filename = N'C:\Users\Administrator\Desktop\Results.txt', -- nvarchar(260)
306 @query_result_separator = ',', -- char(1)
307 @append_query_error = 1, -- bit
308 @query_result_no_padding = 0 –bit
309
310 ---------------监控GPOSDB-------------------------------------------
311 EXEC [msdb].[dbo].[sp_send_dbmail]
312 @profile_name = '监控GPOSDB 2012-11-11' ,
313 @recipients = 'xxxxx@163.com', -- varchar(max) --收件人
314 @subject = N'GPOSDB数据库运行状况', -- nvarchar(255) 标题
315 @body_format = 'text', -- varchar(20) 正文格式可选值：text html
316 @importance = 'HIGH', -- varchar(6) 重要等级邮件重要性必须为LOW、NORMAL 或HIGH
317 @sensitivity = 'NORMAL', -- varchar(12) 敏感度邮件敏感度必须为NORMAL、PERSONAL、PRIVATE 或CONFIDENTIAL。
318 @query = N'DBCC CHECKDB([GPOSDB])', -- nvarchar(max)
319 @execute_query_database = gposdb, -- sysname
320 @query_result_separator = ',', -- char(1)
321 @append_query_error = 1, -- bit
322 @query_result_no_padding = 0 --bit
323
324
325
326 创建好之后放入维护计划！！！！！！！
327
328
329
330
331 6. 查看邮件发送情况：
332
333 use msdb
334 go
335 select * from sysmail_allitems
336 select * from sysmail_mailitems
337 select * from sysmail_event_log
338
339
340
341 如果不是以 sa 帐户发送邮件，则可能会出现错误：
342 Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
343 EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
344
345 这是因为，当前 SQL Server 登陆帐户（login），在 msdb 数据库中没有发送数据库邮件的权限， 需要加入 msdb 数据库用户，并通过加入 sp_addrolemember 角色赋予权限。假设该SQL Server 登陆帐户 名字为 “dba”
346
347 use msdb
348 go
349 create user dba for login dba
350 go
351 exec dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole',
352 @membername = 'dba'
353 Go
354
355
356
357
358
359 此时，再次发送数据库邮件，仍可能有错误：
360 Msg 14607, Level 16, State 1, Procedure sp_send_dbmail, Line 119
361 profile name is not valid
362 虽然，数据库用户 “dba” 已经在 msdb 中拥有发送邮件的权限了， 但这还不够，他还需要有使用 profile：“dba_profile” 的权限
363
364
365 USE msdb
366 go
367
368 EXEC sysmail_add_principalprofile_sp @principal_name = 'dba',
369 @profile_name = 'joe_profile2012-11-11', @is_default = 1
370
371
372 从上面的参数 @is_default=1 可以看出，一个数据库用户可以在多个 mail profile 拥有发送权限。
373
374
375
376 只有 sysadmin 固定服务器角色的成员才能使用“发送测试电子邮件”对话框。如果用户不是 sysadmin 固定服务器角色的成员，可以使用 sp_send_dbmail 过程来测试数据库邮件。
377 JOE\Administrator是sysadmin角色
