SQLMail的原理及实际应用

早知

　　-概述 　　 　　----这里所指的SQLMail，是微软公司的关系数据库管理系统 SQLServer所提供的邮件功能。在SQLServer中包含一些扩展存储过程，使得SQLServer可以通过基于WindowsNT内部的消息应用编程界面（MAPI）的客户机邮件程序接收和发送邮件消息。SQLMail可发送的消息包括文本串，附加文件或SQL语句的执行结果集。应用SQLMail的扩展存储过程，邮件消息可以从一个触发 　　器或一个存储过程中发送，并且通过SQLServer提供的任务和警告等功能，可以根据需要在不同的时间和情况下发送。 　　 　　----SQLMail工作流程 　　 　　----SQLMail工作流程主要分为邮件收、发两个过程。 　　 　　----在SQLServer内可以通过一定方法，调用扩展存储过程xp_s 　　endmail，将收信人电子邮件地址，标题，信的内容等以参数的形式传送给xp_sendmail，由它通过客户端MAPI接口将发送邮件任 　　务交给如MicrosoftExchangeClient或Outlook97等邮件客户端程序，最终通过邮件服务将邮件发送出去。 　　 　　----收邮件的过程与此相似。在SQLServer端定期或手工调用xp_readmail扩展存储过程，通过客户端MAPI接口将存放在邮件服务器或客户端的邮件及其他信息读入SQL变量中，用来满足特定的处理需要。 　　配置SQLMail 　　 　　----配置SQLMail的首要条件是要有一个电子邮件帐户，不管是局域网内的MSExchangeServer或MSMail3.x的邮件帐户，还是Internet上的SMTP，POP3等邮件帐户均可。具体配置可分为以下几步： 　　 　　----1．为SQLServer创建一个NT域用户帐号，要求具有本地管理员组的权限。仔细设置帐号口令，保证口令足够复杂，且设成口令永不过期和用户不得更改口令。 　　 　　----2．双击控制面板的服务图标，找到MSSQLServer的启动设置，将UseSystemAccount改为UseThisAccount，将刚才建立的NT帐号和口令输入。重新启动SQLServer。 　　 　　----3．在安装SQLServer的服务器上以与启动SQLServer相同的NT帐号登录，然后安装支持MAPI接口的邮件客户端程序，如MS 　　ExchangeClient和Outlook97。 　　 　　----4．打开控制面板的电子邮件选项，建立一个配置文件（Profile）。用这个配置文件启动邮件客户端程序，反复检验直至能正常收发邮件。 　　 　　----5．从SqlEnterpriserManager中，点中相应的服务器图标，从 　　Server菜单中选SQLMail/Configure，将在第4步中建立的配置文件名输入。 　　 　　----6．从SqlEnterpriserManager中，点中相应的服务器图标，从 　　Server菜单中选SQLMail/Start，如果SqlMail的图标变为绿色，则SQ 　　LMail成功启动。 　　 　　----常用的SQLMail扩展存储过程 　　 　　xp_sendmail@recipient=recipient 　　 　　[;recipient2;[...;recipientn]] 　　 　　[,@message=message] 　　 　　[,@query=query] 　　 　　[,@attachments=attachments] 　　 　　[,@copy_recipients=recipient 　　 　　[;recipient2;[...;recipientn]]] 　　 　　[,@blind_copy_recipients=recipient 　　 　　[;recipient2;[...;recipientn]]] 　　 　　[,@subject=subject] 　　 　　[,@type=type] 　　 　　[,@attach_results={'true'|'false'}] 　　 　　[,@no_output={'true'|'false'}] 　　 　　[,@no_header={'true'|'false'}] 　　 　　[,@width=width] 　　 　　[,@separator=separator] 　　 　　[,@echo_error={'true'|'false'}] 　　 　　[,@set_user=user] 　　 　　[,@dbuse=dbname] 　　 　　----此存储过程通过客户端MAPI接口发送邮件，内容可以是文本串，附加文件或SQL语句的执行结果集。 　　 　　xp_findnextmsg[@msg_id=msg_id[OUTPUT]] 　　 　　[,@type=type] 　　 　　[,@unread_only={'true'|'false'}]) 　　 　　----此存储过程在邮箱中查找特定的邮件，并返回一封邮件的消息ID。 　　 　　xp_readmail([@msg_id=msg_id][,@type=type[OUTPUT]] 　　 　　[,@peek={'true'|'false'}] 　　 　　[,@suppress_attach={'true'|'false'}] 　　 　　[,@originator=@senderOUTPUT] 　　 　　[,@subject=@subject_lineOUTPUT] 　　 　　[,@message=@body_of_messageOUTPUT] 　　 　　[,@recipients=@recipient_listOUTPUT] 　　 　　[,@cc_list=@cc_listOUTPUT] 　　 　　[,@bcc_list=@bcc_listOUTPUT] 　　 　　[,@date_received=@dateOUTPUT] 　　 　　[,@unread={'true'|'false'}] 　　 　　[,@attachments=@temp_file_pathsOUTPUT]) 　　 　　[,@skip_bytes=@bytes_toskipOUTPUT] 　　 　　[,@msg_length=@length_in_bytesOUTPUT]) 　　 　　----此存储过程从指定的邮件收件箱中读取指定消息ID的邮件的各项信息。 　　 　　----xp_deletemail[@msg_id=]msg_id 　　 　　----从邮件收件箱中删除一封指定消息ID的邮件。 　　 　　----注释： 　　 　　----1.存储过程的多个参数间用逗号间隔开，[]内的为可选参数，每个参数均以@符号加字符串开头，用以区别不同的参数项。等号后可以是常量，也可以是预先定义好的变量。 　　 　　----2.如果需要将某个结果值赋予参数中预先定义好的变量，就必须在该项参数的最后加OUTPUT。 　　 　　----3.常用参数解释： 　　 　　----?@recipient=recipient；指定收件人的电子邮件地址。如果有多个收件人，可以用分号分隔开。 　　 　　----?@subject=subject；邮件的标题。发送邮件时的默认值为 　　"SQLServerMessage"。 　　 　　----?@message=message；邮件的具体内容。 　　 　　----?@attachments=attachments；邮件挂接的附加文件名。 　　 　　----?@type=type；基于MAPI定义的消息类型，详细信息参见" 　　MicrosoftWindowsNTResourceKit"或"MicrosoftMailTechnicalReference"。 　　 　　----?@query=query；一条SQL可执行语句，其执行结果以正文或附件的方式随邮件发送。 　　 　　----?@msg_id=msg_id；对于信箱中的每一封邮件均被分配了 　　 　　----?@attachments=attachments；邮件挂接的附加文件名。 　　 　　----?@type=type；基于MAPI定义的消息类型，详细信息参见" 　　MicrosoftWindowsNTResourceKit"或"MicrosoftMailTechnicalReference"。 　　 　　----?@query=query；一条SQL可执行语句，其执行结果以正文或附件的方式随邮件发送。 　　 　　----?@msg_id=msg_id；对于信箱中的每一封邮件均被分配了一个特殊的消息ID，用以相互区分。 　　 　　----?@originator=@sender；读取特定邮件的发送者的邮件地址。 　　 　　--基于SQLMail的电子报刊自动处理系统 　　 　　----这里利用SQLMail简单的实现了一个电子报刊自动处理系统。每当新的一期电子报刊编辑完成，只需简单追加到发行数据库publication中，SQLServer会自动定期执行my_publish存储过程。在my_publish存储过程中，它检查发行数据库，当发现有新的记录（即新的一期电子报刊）时，就通过逐个从订阅数据库sub_info中取出订阅人的邮件地址，完成给每个订阅者发送电子报刊（以邮件形式）的任务。 　　 　　----电子报刊的订阅与取消也是通过邮件来实现。SQLServer同样定期执行my_subscibe存储过程。my_subscibe存储过程检查特定 　　邮箱中的所有邮件，如果存在标题为"subscribe"的邮件，就取出它的发件人等信息，在订阅数据库中添加一条记录；同样，如果存在标题为"stopsubscribe"的邮件，就将它的相关记录从订阅数据库中删除。系统中用到的数据库和存储过程如下所 　　示。sub_info（订阅者信息数据库） 字段名称类型允许空值含义 　　 　　emailvarchar(20)NOTNULL订阅者电子邮件地址 　　sub_datedatetimeNOTNULL订阅时间 　　 　　othertextNULL订阅者的其他信息 　　 　　publication（电子报刊出版数据库） 　　 　　字段名称类型允许空值含义 　　 　　pub_classchar(10)NOTNULL电子报刊期号 　　 　　pub_datedatetimeNULL出版日期 　　 　　titletextNOTNULL本期电子报刊标题 　　 　　contenttextNOTNULL电子报刊正文 　　 　　endnotetextNULL附加于报刊的其他信息 　　 　　flagsmallintNULL1，标志为未出版的新报刊 　　 　　my_subscribe，用于处理订阅者信息的存储过程。 　　 　　CREATEPROCEDUREmy_subscribe 　　 　　AS 　　 　　declare@msg_idvarchar(64) 　　 　　declare@subjectvarchar(255) 　　 　　declare@messagevarchar(255) 　　 　　declare@originatorvarchar(255) 　　 　　declare@datevarchar(255) 　　 　　declare@statusint 　　 　　declare@mapifailureint 　　 　　select@mapifailure=0 　　 　　 　　while(1=1) 　　 　　begin 　　 　　/*查找邮件并获取消息ID*/ 　　 　　exec@status=master.. 　　 　　xp_findnextmsg@msg_id=@msg_idOUTPUT 　　 　　if@status0 　　 　　begin 　　 　　select@mapifailure=1 　　 　　break 　　 　　end 　　 　　if@msg_idisnullbreak 　　 　　/*读取邮件的信息到变量中*/ 　　 　　exec@status=master..xp_readmail 　　 　　@msg_id=@msg_id, 　　 　　@originator=@originatorOUTPUT, 　　 　　@subje <