让我的sql邮件工作

让我的sql邮件工作 SQL Mail是数据库系统同邮件系统进行沟通的桥梁。由于SQL Mail的出现，我们可以开发出广泛的数据库和邮件系统相结合的应用。它可以用来将数据库系统产生的一些警报信息发送给管理员（这是SQL Mail最常用的功能），从而使管理员能够及时地对系统发生的问题作出反应。也可以接受用户发出的查询邮件，并将查询结果通过邮件发送给用户。也可以用它来实现邮件列表的功能。
  下面我们将来介绍如何设置和使用SQL Mail。
6.7.1.  设置SQL Mail
  还记得我们当初在安装SQL Server之前在Exchange Server上为其服务帐号设置的邮箱吗？SQL Mail将通过这个邮箱来发送和接受邮件。而SQL Mail的设置是一个比较复杂的过程。需要为其指定一个邮件配置文件和相应的支持MAPI接口的应用程序（为了使讨论的问题简化，我们使用Outlook作为SQL Mail的MAPI支持程序。当然，也可以使用其他的邮件系统和支持MAPI的邮件客户端程序）。下面我们来看看如何对SQL Server进行设置，以使其SQL Mail服务能够正常的运转起来。
  首先，我们用SQL Server的服务帐号（sqlservice）登录服务器。安装Outlook并设置配置信息（这将产生一个配置文件，SQL Mail将通过此配置文件与邮件系统连接。设置方法参见本书前面的内容）。启动Outlook并对邮件的收发进行测试。确认无误后，退出登录，重新以管理员帐号登录服务器。
  注意：如果安装的是Outlook97，它有可能会在系统的启动文件组中加入一个用于快速查找文件的程序findfast.exe（这取决于你在安装Outlook时所做的对安装组件的选择）。它在每次系统启动时自动运行，会占用大量的服务器资源。请将它从启动程序组中删除。
  启动Enterprise Manager，找到要进行SQL Mail设置的服务器并打开其Support Services容器。右击其下的SQL Mail对象，从弹出的快捷菜单中选择Properties选项。就会弹出SQL Mail属性对话框（如下图）。

图6.7.1－1SQL Mail的设置
  在Profile name下拉列表框中选择我们在前一步建立的邮件配置文件。点击Test按钮来对此设置进行测试。如果测试成功，会出现一个提示SQL Mail设置成功的对话框。如果存在问题，则需要重新执行前面的步骤。直到测试成功。
  Autostart SQL Mail when SQL Server starts选择框用于指定是否在SQL Server启动时也自动地启动SQL Mail。我们应该选择此选项。配置完成后，点击确定按钮结束设置工作。
  随后右击SQL Mail对象，从弹出的快捷菜单中选择Start选项来启动SQL Mail。至此我们就完成了SQL Mail的配置工作。
6.7.2.  通过SQL Mial发送邮件
  SQL Mail会在两种情况之下发送邮件。一种是当系统发生警报时间时，会向预先为警报定义的操作员发送警报信息邮件（我们将在下一节中对此问题做详细的讨论）。另一种是通过扩展存贮过程xp_sendmail来发送邮件。如果我们想要在SQL Server应用中完成发送邮件功能的话，就需要使用此存贮过程。它的语法结构如下：
  xp_sendmail {[@recipients =] 'recipients [;...n]'}
  [,][@message =] 'message']
  [,][@query =] 'query']
  [,][@attachments =] attachments]
  [,][@copy_recipients =] 'copy_recipients [;...n]'
  [,][@blind_copy_recipients =] 'blind_copy_recipients [;...n]'
  [,][@subject =] 'subject']
  [,[@type =] 'type']
  [,][@attach_results =] 'attach_value']
  [,][@no_output =] 'output_value']
  [,][@no_header =] 'header_value']
  [,][@width =] width]
  [,][@separator =] 'separator']
  [,][@echo_error =] 'echo_value']
  [,][@set_user =] 'user']
  [,][@dbuse =] 'database']
  其中各参数的含义分别如下：
■  recipients：为邮件指定的接收人，可以同时将邮件发给多个用户。各用户的邮件地址用分号分割。此参数必须指定，而其他参数都为可选参数。
■  message：邮件中的信息，其最长不得超过7990字节。
■  query：一段SQL语句，其结果集将附在邮件之中。它的长度不得超过8000字节。
■  attachments：用来指定一个文件作为被发送邮件的附件。
■  copy_recipients：用来指定此邮件抄送到哪些用户。即我们在邮件程序中常见的CC to:功能。
■  blind_copy_recipients：功能同上个参数。不过不同的是，邮件之中将不会包含这些收件人的地址信息。即我们在邮件程序中常见的密送BCC to:功能。
■  subject：邮件的标题。如果不指明，默认为"SQL Server Message"。
■  type：指定邮件的MAPI类型。默认为空值。
■  attach_results：指明是否将SQL语句的执行结果作为一个附件进行发送。其取值为TRUE或FALSE。默认值为FALSE。
■  no_output：指明此发送将不返回信息到SQL 客户端。其取值为TRUE或FALSE。默认值为FALSE。
■  no_header：指明不将SQL语句执行结果中的字段名包含在邮件之中。其取值为TRUE或FALSE。默认值为FALSE。
■  width：指定SQL语句执行结果的行宽。省缺宽度为80个字符这样可能会造成一些结果行被从中间截断的现象。将行宽设大一些可以有效地防止此现象。
■  separator：指定SQL语句的执行结果进行字段分割的字符。这样会方便一些电子表格应用程序读取结果集。
■  echo_error：指明是否包含系统错误信息。其取值为TRUE或FALSE。默认值为TRUE。
■  set_user：指明执行SQL语句的用户，默认为guest。此参数所指定的用户应该在master数据库中存在。
■  dbuse：指明SQL语句在哪个数据库上执行，默认为set_user指定的用户的省缺数据库。
下面的例子将把1999年12月1日的所有刊载的文章的题目发送给邮件地址为zw@cbb.com的用户。
exec master.dbo.xp_sendmail
  @recipients ='zw@cbb.com',
  @message =񟬿年12月1日刊载文章题目' ,
  @query = 'select timu
from test.dbo.gaojian
where datepart(yy,riqi)=datepart(yy,convert(datetime,'񟬿-12-01')) and datepart(dy,riqi)=datepart(dy,convert(datetime,'񟬿-12-01'))
order by banmian' ,
  @subject ='test',
  @attach_results = 'true',
  @width =60,
  @separator = ',' ,
  @set_user ='dbo',
  @dbuse = 'test'
  zw用户收到的邮件附件文件的内容如下：
  Timu   ,
----------------------------------------------------------------------------------------
测试信息1                         ,
测试信息2                         ,
测试信息3                         ,
·
·
·

6.7.3.  SQL Mail对邮件的接收
  SQL Mail接收邮件使用xp_readmail存贮过程。其语法格式如下：
  xp_readmail [][@msg_id =] 'message_number'] [, ][@type =] 'type' [OUTPUT]]
  [,[@peek =] 'peek']
  [,][@suppress_attach =] 'suppress_attach']
  [,][@originator =] 'sender' OUTPUT]
  [,][@subject =] 'subject' OUTPUT]
  [,][@message =] 'message' OUTPUT]
  [,][@recipients =] 'recipients [;...n]' OUTPUT]
  [,][@clearcase/" target="_blank" >cc_list =] 'copy_recipients [;...n]' OUTPUT]
  [,][@bcc_list =] 'blind_copy_recipients [;...n]' OUTPUT]
  [,][@date_received =] 'date' OUTPUT]
  [,][@unread =] 'unread_value' OUTPUT]
  [,][@attachments =] 'attachments [;...n]' OUTPUT])
  [,][@msg_length =] length_in_bytes OUTPUT]
  [,][@originator_address =] 'sender_address' OUTPUT]]
  邮件的接收是一个复杂的过程，xp_readmail存贮过程在读取邮件之后将邮件的信息放到各个参数之中。各参数的含义分别如下（参数后的OUTPUT指明将读到的参数放到输出参数中，即此参数为输出参数）：
■  msg_id：指定要读取的邮件的编号。
■  type：邮件的MAPI类型。为输出参数。
■  peek：是否将邮件标志为已读。其取值为TRUE或FALSE。默认值为FALSE。
■  suppress_attach：指明是否禁止为邮件的附件建立临时文件。其取值为TRUE或FALSE。默认值为TRUE。
■  originator：邮件发送者的回复邮件地址。为输出参数。
■  subject：邮件的标题。为输出参数。
■  message：邮件体，通常是一个SQL语句。最大长度为8000字节。为输出参数。
■  recipients：邮件的收件人地址。如果为多个地址，各地址之间用分号分隔。为输出参数。
■  cc_list：邮件的抄送地址列表。为输出参数。
■  bcc_list：邮件的密送地址列表。为输出参数。
■  date_received：邮件的接收日期。为输出参数。
■  unread：邮件是否已读。为输出参数。
■  attachments：保存邮件的各个附件的临时目录。为输出参数。
■  msg_length：邮件信息的长度。为输出参数。
■  originator_address：邮件发送者的邮件地址。为输出参数。
下面的例子为读取一个编号为Ƈ$T6;J00'的邮件并执行其中的查询，将查询结果返回给邮件的发送者：
declare @msgsubject varchar(255)
declare @query varchar(255)
declare @messages int
declare @mapifailure int
declare @resultmsg varchar(80)
declare @filename varchar(12)
declare @current_msg varchar(64)

exec master.dbo.xp_readmail
    @msg_id=Ƈ$T6;J00',
    @originator=@originator output,
    @cc_list=@cc_list output,
    @subject=@msgsubject output,
    @message=@query output,
    @peek='true'

exec master.dbo.xp_sendmail
    @recipients=@originator,
    @copy_recipients=@cc_list,
    @message=@query,
    @query=@query,
    @subject='Query Results',
    @width=256,
    @attachments='QueryResults.txt',
    @attach_results='true',
    @no_output='false',
    @echo_error='true',
    @set_user='dbo',
    @dbuse='test'
在语句之中我们使用了declare语句。它是用来在SQL语句中定义变量的。我们通过定义的变量来完成存贮过程之间参数的传递。
默认情况下（不指明msg_id）xp_readmail存贮过程将读取当前收件箱中所有的邮件。而邮件的编号是一个随机的字符串，我们通常是不知道一个邮件的编号的。而我们可以通过xp_findnextmsg存贮过程来得到一个邮件的编号。其语法结构如下：
  xp_findnextmsg [][@msg_id =] 'message_number' [OUTPUT]]
  [,[@type =] type]
  [,][@unread_only =] 'unread_value'])
其中msg_id参数为邮件的编号，既是输入参数，也是输出参数。当指明其作为输出参数时（其后跟OUTPUT关键字），参数变量将被赋予其下一个邮件的编号。如果当然参数已经是最后一条邮件的编号，则会返回一个空值。
而type参数则指明邮件的类型。
unread_only参数指明是否忽略那些已经被标志为已读的邮件。其取值为TRUE或FALSE。默认值为FALSE。
下面的例子为使用xp_findnextmsg存贮过程来读取一条邮件的编号后，再读取它的下一条邮件的编号。：
declare @msg_id varchar(64)
set @msg_id='
exec master.dbo.xp_findnextmsg @msg_id=@msg_id output
select @msg_id
exec master.dbo.xp_findnextmsg @msg_id=@msg_id output
select @msg_id
其运行结果如下：
----------------------------------------------------------------
1$:=D\"P

(1 row(s) affected)

----------------------------------------------------------------
1$T6;J00

(1 row(s) affected)
可以看到，它返回了两个邮件的编号。
而我们还可以通过xp_deletemail存贮过程来完成邮件的删除工作。其语法结构如下：
xp_deletemail {'message_number'}
其调用十分简单，只要在message_number参数中指明要删除的邮件的编号即可。比如我们要删除编号为"1$:=D\"P"的邮件，只需运行下面的语句：
exec master.dbo. xp_deletemail Ƈ$:=D\"P '
从上面的例子看来，SQL Mail的使用似乎非常复杂。但是我们可以使用sp_processmail存贮过程来完成对邮件的批量处理。sp_processmail的执行过程是这样的：首先调用xp_readmail，读取邮件。如果邮件中包含有效的SQL语句，则执行它并调用xp_sendmail将结果以附件的形式发送到此邮件的回复地址。随后调用xp_deletemail将此邮件删除。然后再调用xp_findnextmsg来进行下一个邮件的读取。此反复过程将一直进行到处理完收件箱中所有的邮件为止。sp_processmail的语法格式如下：
sp_processmail [][@subject =] 'subject']
  [,][@filetype =] 'filetype']
  [,][@separator =] 'separator']
  [,][@set_user =] 'user']
  [,][@dbuse =] 'dbname']
其中各参数都为输入参数。Subject用于指定只处理那些标题与此参数相同的邮件。如果为空，则处理收件箱中所有标记为未读的邮件（可以通过修改sp_processmail的代码而让其处理收件箱中所有的邮件）。
Filetype参数指明结果附件的扩展名，默认为txt。
Separator参数用来指定结果中的字段分割符，默认为tab。
set_user和dbuse参数的含义同xp_readmail中的同名参数。
注意：set_user所指明的参数必须在master数据库中存在。而sp_processmail中存在一个错误。当你为set_user指明一个特定的值或变量时（这在数据库中没有GUEST用户或要运行没有授予GUEST用户相应权限的SQL语句时，是必须指明的），可能会得到下面的错误信息：
Server: Msg 18007, Level 16, State 1, Line -2122745264
Supplied datatype for @set_user is not allowed, expecting 'varchar'
这是由于sp_processmail中的一个参数的类型匹配错误造成的。我们在sp_processmail代码的头部可以看到如下的参数定义：
create procedure sp_processmail --- 1996/06/19 17:30
  @subject varchar(255)=NULL,
  @filetype varchar(3)='txt',
  @separator varchar(3)='tab',
  @set_user sysname='guest',
  @dbuse sysname='master'
·
·
·
其中用下划线标出的就是引起错误的参数定义语句。Sysname为SQL Server定义的一种用户自定义型数据类型，它和nvarchar(128)是等效的。而sp_processmail要调用的xp_sendmail存贮过程的set_user参数需要varchar类型与之匹配。因此就产生了上面的错误。而我们只要将上面用下划线标出的两句改为如下的语句即可：
  @set_user varchar(255)='guest',
  @dbuse varchar(255)='master'
可以通过运行本书配套光盘SQLServer目录下的mailerror.sql脚本文件即可除去此错误。
下面的例子将处理收件箱中所有题目为"test"的未读邮件。
exec master.dbo.sp_processmail
  @subject='test',
  @filetype= 'txt',
  @separator=',',
  @set_user='dbo',
  @dbuse ='test'
sp_processmail存贮过程一般用于定时执行的任务之中，来处理用户通过邮件提交的查询。同任务有关的内容将在后面进行介绍。
6.8.  通过代理服务简化对SQL Server的管理
  对系统管理员来说，对于SQL Server的日常管理工作是非常烦琐的。比如对数据库的备份、系统运行状态的监视、一些需要在特定时间执行的任务等。而SQL Server的代理服务可以以在很大的程度上减轻系统管理员的工作量。它作为WindowsNT的一个服务来运行，可以自动执行系统管理员预先安排的各种管理任务、监视SQL Server事件、并根据事件触发警报。SQL Server代理服务的另一个特点是能够集中管理多个SQL Server服务器。管理员在可以一台服务器上定义任务并将其下达给其他服务器执行，而这些服务器产生的事件又可以转发给这台服务器进行集中处理。
  SQL Server代理服务由以下三个部件组成：
■  (alerts)警报：警报是管理员所定义的对SQL Server事件的响应动作，它可以是对操作员的通知，或产生SNMP中断，执行任务等。
■  (operators)操作员：接收警报事件通知的人员（通常为系统管理员），他们可根据收到的通知内容来检查问题所在，并将其解决。
■  (jobs)任务：需要在特定时刻或时机执行的操作，它可以是SQL语句、系统命令、应用程序、ActiveScript或复制代理等。其既可以用于管理工作，也可用于其他目的。
在SQL Server中，警报、事件和任务三者相互关连，事件可以触发警报、警报可以激发任务、任务又可产生事件。任务调度、操作员事件处理和警报管理组成SQL Sever代理服务的分布式管理环境。
  下面将介绍如何对SQL Sever代理服务进行设置和使用其中的各个部件。

原文转自：http://www.ltesting.net