如果不使用 SQL Mail,如何在 SQL Server 中发送电子邮件

1、如果不使用 SQL Mail，如何在 SQL Server 中发送电子邮件默认分类 2010-01-08 11:51:02 阅读32 评论0   字号：大中小 订阅 SQL Mail 提供了一种从 Microsoft SQL Server 发送和阅读电子邮件的简单方法。但是，由于 SQL Mail 是一个 MAPI 应用程序，因此服务器上必须存在 MAPI 子系统。Microsoft Windows NT 4.0 会在您安装 Windows Messaging 时安装 MAPI 子系统。但是，Microsoft Windows 2000 不提供 MAPI 子系统。

2、因此，如果您需要使用 SQL Mail，就必须安装诸如 Microsoft Outlook 之类的 MAPI 客户端。您可以使用其他方法从 SQL Server 中直接发送简单邮件传输协议 (SMTP) 电子邮件。例如，可以配合 sp_OA SQL Server OLE 自动化存储过程使用 NT Server 协作数据对象 (CDONTS) 或 Windows 2000 协作数据对象 (CDOSYS)。本文将举例说明如何使用这些方法向 Internet 邮件服务器发送电子邮件。您可以修改这些方法以便它们能够提供更可靠的邮件系统。例如，您可能需要添加错误处理代码。这些示例中的方法不提供阅读或处理

3、电子邮件的方法。注意：从 Windows 2000 起开始提供 CDOSYS，因此，建议您用 CDOSYS 来代替 CDONTS。Windows Server 2003 及更高版本的操作系统都不支持 CDONTS。注意：这些编程示例阐释了您可以用来在 Transact-SQL 中调用 CDO 对象模型的方法。您要负责对这些示例进行任何编程扩展，或者负责进行在生产环境中运行代码所需的任何压力测试。Microsoft 提供的编程示例只用于说明目的，不附带任何明示或默示的保证。这包括但不限于对适销性或特定用途适用性的默示保证。本文假定您熟悉所演示的编程语言和用于创建和调试过程的工具。Microsof

4、t 的支持工程师可以帮助解释某个特定过程的功能，但是他们不会修改这些示例以提供额外的功能或构建过程以满足您的特殊需求。 回到顶端如何使用 CDO for Microsoft Windows NT Server (CDONTS)CDONTS 是一个特定于 OLE 服务器的简单邮件传输协议 (SMTP)，专门用于向基于 Web 的应用程序提供邮件传输功能。因此，CDONTS 支持发送基于 HTML 的电子邮件。基于 Mapi 的应用程序（如 SQL Mail）则不支持。默认情况下，Microsoft Internet Information Server (IIS) 4.0 或更高版本会安装 CD

5、ONTS。默认情况下，Microsoft Windows 2000 会安装 Microsoft Internet Information Server (IIS) 5.0。有关 CDONTS 的更多信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 177850  ( ) CDO 1.2 和 CDONTS 之间的区别是什么？ CDONTS 通过 SMTP 向本地服务器发送电子邮件。如果您的本地服务器上没有 SMTP 服务器，可以使用 IIS 安装的 SMTP 虚拟服务器将您的 SMTP 电子邮件路由到适当的 SMTP 邮件服务器上。要使用 CDONTS 从

6、SQL Server 中发送电子邮件，请按照下列步骤操作： 1. 安装 IIS 并在运行 SQL Server 的计算机上运行它。 2. 将您的 SMTP 邮件服务器指定为您的“智能主机”，以便 IIS SMTP 服务自动将发送到本地服务器的任何 SMTP 电子邮件路由到您的 SMTP 邮件服务器上进行传送。 3. 在 SQL Server 中创建一个可用来发送电子邮件的存储过程。当您使用 CDONTS 而不是 SQL Mail 时，不必在运行 SQL Server 的计算机上安装诸如 Microsoft Outlook 之类的邮件客户端。您也不必非得有一个 Microsoft Exchang

7、e 服务器。您可以将任何支持 SMTP 电子邮件的邮件服务器用作您的“智能主机”。但是，您将无法阅读和处理使用 CDONTS 发送到运行 SQL Server 的计算机上的电子邮件，也不能替代 SQL Agent Mail 的功能。下面的说明是针对 Microsoft Windows 2000 的。如果计算机上安装了 IIS 4.0，可以使用类似的方法配置 Microsoft Windows NT 4.0。不过，所采取的步骤会略有不同。有关如何在 Microsoft Windows NT 4.0 上配置“智能主机”的更多信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章

8、： 230235  ( ) 如何将 IIS SMTP 服务配置为中继 SMTP 邮件 配置智能主机1. 单击“开始”，指向“程序”，指向“管理工具”，然后单击“Internet 服务管理器”以打开 IIS 管理器。 2. 打开您的服务器树。右键单击“默认 SMTP 虚拟服务器”，然后单击“属性”。 3. 找到“传递”选项卡，然后单击“高级”。 4. 在“智能主机”文本框中键入您的 SMTP 邮件服务器的名称。如果您不知道 SMTP 邮件服务器的名称，请与您的邮件管理员联系。 5. 确保简单邮件传输协议 (SMTP) 服务正在运行。SMTP 服务是 IIS 管理服务的一部分。因此，II

9、S 管理服务也必须正在运行。创建发送 CDONTS 电子邮件的存储过程注意：本文中作为示例列出的公司、组织、产品、域名、电子邮件地址、徽标、人名、地名和事件完全是虚构的。决无意影射，也不应由此臆测任何真实的公司、组织、产品、域名、电子邮件地址、徽标、人名、地名或事件。您可以使用类似如下的代码在数据库中创建一个存储过程，使之通过使用 SQL Server OLE 自动化存储过程调用 CDONTS 对象模型来发送电子邮件。DE>CREATE PROCEDURE dbo.sp_send_cdontsmail From varchar(100),To varchar(100),Subject v

10、archar(100),Body varchar(4000),CC varchar(100) = null,BCC varchar(100) = nullASDeclare MailID intDeclare hr intEXEC hr = sp_OACreate 'CDONTS.NewMail', MailID OUTEXEC hr = sp_OASetProperty MailID, 'From',FromEXEC hr = sp_OASetProperty MailID, 'Body', BodyEXEC hr = sp_OASetProp

11、erty MailID, 'BCC',BCCEXEC hr = sp_OASetProperty MailID, 'CC', CCEXEC hr = sp_OASetProperty MailID, 'Subject', SubjectEXEC hr = sp_OASetProperty MailID, 'To', ToEXEC hr = sp_OAMethod MailID, 'Send', NULLEXEC hr = sp_OADestroy MailIDDE>接下来使用您刚创建的存储过程并提供正确的参数

12、： DE>exec sp_send_cdontsmail 'someone','someone2','Test of CDONTS','It works'DE>注意：只有“sysadmin”固定服务器角色的成员才可以运行 OLE 自动化存储过程。如果 SQL Server 用户不是“sysadmin”固定服务器角色的成员，则不能使用本例中提到的存储过程来发送电子邮件。在这种情况下，您可能必须开发一个客户端应用程序来通过 CDONTS 发送电子邮件。例如，您可以使用 Microsoft Visual Basic 应用程序

13、。CDONTS 将电子邮件发送到本地 SMTP 虚拟服务器中。该服务器随后将该电子邮件路由到“智能主机”文本框中指定的 SMTP 邮件服务器中。SMTP 邮件服务器将邮件发送到“To:”参数中指定的电子邮件地址（本例中为“someone2”）。“From:”参数中指定的名称是电子邮件的发件人（本例中为“someone”），“Test of CDONTS”是电子邮件的主题，而消息“It works”是电子邮件的正文。此电子邮件没有抄送给他人，因为您没有为“CC”或“BCC”字段提供参数。您可以修改本例，以便它能够发送基于 HTML 的电子邮件或附件。如需关于 CDONTS 的文档，请访问下面的

14、Microsoft 网站： ( 在左窗格中，展开“消息和协作”，展开“协作数据对象”，然后展开“CDO 1.2.1”。您可以在 SQL Server 2000 联机丛书中找到有关 SQL Server OLE 自动化存储过程的文档。回到顶端如何使用 CDO for Microsoft Windows 2000 (CDOSYS)CDOSYS 为在 Microsoft Windows 2000 中开发邮件传递应用程序提供了一种对象模型。它还提供比现有的 CDO for Windows NT Server (CDONTS) 库更强的功能。CDOSYS 需要 Windows 2000 和一台本地或远程

15、 SMTP 服务器。您可以通过编程方式将 CDOSYS 配置为指向 SMTP 服务器，以使开发人员能够灵活地配置 SMTP 服务器。有关 CDOSYS 的更多信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 195683  ( ) 1.x CDO 库和 CDOSYS.DLL 之间的关系 因为 CDOSYS 可以向远程 SMTP 服务器发送电子邮件，因此 CDOSYS 不需要您在运行 SQL Server 的计算机上安装并运行 Internet Information Server。您也不必配置 SMTP 虚拟服务器。您要做的只是在 SQL Server 中

16、创建一个可以用来发送电子邮件的存储过程。当您使用 CDOSYS 而不是 SQL Mail 时，不必在运行 SQL Server 的计算机上安装诸如 Microsoft Outlook 之类的邮件客户端。您也不必非得有一个 Exchange 服务器。您可以将任何支持 SMTP 邮件的邮件服务器用作您的远程 SMTP 邮件服务器。但是，如果使用 CDOSYS，您将无法阅读或处理发送到 SQL Server 的电子邮件，也不能替代 SQL Agent Mail 的功能。结合 SQL Server OLE 自动化对象使用 CDOSYS 依赖于 SQL Server OLE 自动化来调用 CDOSYS

17、对象模型。已在 SQL Server 2000 Service Pack 1 (SP1) 和 SQL Server 2000 Service Pack 2 (SP2) 中对此进行了测试。Microsoft 不保证能够从在 SQL Server 2000 SP1 之前发布的 SQL Server 版本中的 OLE 自动化存储过程调用 CDOSYS。 创建发送 CDOSYS 邮件的存储过程您可以使用类似如下的代码在数据库中创建一个存储过程，使之通过使用 SQL Server OLE 自动化存储过程调用 CDOSYS 对象模型来发送电子邮件。在本例中，我们提供一个不同程度的错误处理来检查每条 EXE

18、C sp_OA* 语句的 hresult。如果 hresult (hr) 为零，则前一条 sp_OA* 语句成功。但是，如果该 hresult 不为零，则前一条 sp_OA* 语句失败。对于每一个非零 hresult 故障，都会在表 dbo.cdosysmail_failures 中插入一行来注明故障的位置和时间。另外，通过使用 sp_OAGetErrorInfo 存储过程来报告错误根源和描述，可以解释最后一个错误。该错误描述输出到客户端连接。同样，故障的错误描述、时间和位置也插入数据库的表 dbo.cdosysmail_failures 中，以便您可以在事实发生后确定和诊断故障。如果 sp_

19、OAGetErrorInfo 存储过程本身失败，该示例会将下面的错误消息显示到客户端连接： sp_OAGetErrorInfo failedDE>- drop old cdosysmail_failures table if existsIF (EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'cdosysmail_failures' AND type='U') DROP TABLE dbo.cdosysmail_failuresGO- Create new cdosysmail_failures ta

20、bleCREATE TABLE dbo.cdosysmail_failures(Date of Failure datetime, Spid int NULL,From varchar(100) NULL,To varchar(100) NULL,Subject varchar(100) NULL,Body varchar(4000) NULL,iMsg int NULL,Hr int NULL,Source of Failure varchar(255) NULL,Description of Failure varchar(500) NULL,Output from Failure var

21、char(1000) NULL,Comment about Failure varchar(50) NULL)GOIF (EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'sp_send_cdosysmail' AND type='P') DROP PROCEDURE dbo.sp_send_cdosysmailGOCREATE PROCEDURE dbo.sp_send_cdosysmail From varchar(100) , To varchar(100) , Subject varchar(100)

22、=" ", Body varchar(4000) =" "/*This stored procedure takes the parameters and sends an e-mail. All the mail configurations are hard-coded in the stored procedure. Comments are added to the stored procedure where necessary.References to the CDOSYS objects are at the following MSDN

23、 Web site:*/ AS Declare iMsg int Declare hr int Declare source varchar(255) Declare description varchar(500) Declare output varchar(1000)-* Create the CDO.Message Object * EXEC hr = sp_OACreate 'CDO.Message', iMsg OUT IF hr <>0 BEGIN SELECT hr INSERT INTO dbo.cdosysmail_failures VALUES

24、 (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, description, output, 'Failed at sp_OACreate') EXEC hr = sp_OAGetErrorInfo NULL, source OUT, description OUT IF hr = 0 BEGIN SELECT output = ' Source: ' + source PRINT output SELECT output = ' Description: ' + descr

25、iption PRINT output INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, description, output, 'sp_OAGetErrorInfo for sp_OACreate') RETURN END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END END-*Configuring the Message Objec

26、t *- This is to configure a remote SMTP server.- EXEC hr = sp_OASetProperty iMsg, 'Configuration.fields(" IF hr <>0 BEGIN SELECT hr INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, description, output, 'Failed at sp_OASetPrope

27、rty sendusing') EXEC hr = sp_OAGetErrorInfo NULL, source OUT, description OUT IF hr = 0 BEGIN SELECT output = ' Source: ' + source PRINT output SELECT output = ' Description: ' + description PRINT output INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subje

28、ct, Body, iMsg, hr, source, description, output, 'sp_OAGetErrorInfo for sp_OASetProperty sendusing') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END- This is to configure the Server Name or IP address. - Replace Mail

29、ServerName by the name or IP of your SMTP Server. EXEC hr = sp_OASetProperty iMsg, 'Configuration.fields(" cdoSMTPServerName IF hr <>0 BEGIN SELECT hr INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, description, output, 'Fail

30、ed at sp_OASetProperty smtpserver') EXEC hr = sp_OAGetErrorInfo NULL, source OUT, description OUT IF hr = 0 BEGIN SELECT output = ' Source: ' + source PRINT output SELECT output = ' Description: ' + description PRINT output INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), s

31、pid, From, To, Subject, Body, iMsg, hr, source, description, output, 'sp_OAGetErrorInfo for sp_OASetProperty smtpserver') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END- Save the configurations to the message object

32、. EXEC hr = sp_OAMethod iMsg, 'Configuration.Fields.Update', null IF hr <>0 BEGIN SELECT hr INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, description, output, 'Failed at sp_OASetProperty Update') EXEC hr = sp_OAGetErrorI

33、nfo NULL, source OUT, description OUT IF hr = 0 BEGIN SELECT output = ' Source: ' + source PRINT output SELECT output = ' Description: ' + description PRINT output INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, description, out

34、put, 'sp_OAGetErrorInfo for sp_OASetProperty Update') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END- Set the e-mail parameters. EXEC hr = sp_OASetProperty iMsg, 'To', To IF hr <>0 BEGIN SELECT hr INSE

35、RT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, description, output, 'Failed at sp_OASetProperty To') EXEC hr = sp_OAGetErrorInfo NULL, source OUT, description OUT IF hr = 0 BEGIN SELECT output = ' Source: ' + source PRINT output SE

36、LECT output = ' Description: ' + description PRINT output INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, description, output, 'sp_OAGetErrorInfo for sp_OASetProperty To') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT '

37、sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END EXEC hr = sp_OASetProperty iMsg, 'From', From IF hr <>0 BEGIN SELECT hr INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, description, output, 'Failed at sp_OAS

38、etProperty From') EXEC hr = sp_OAGetErrorInfo NULL, source OUT, description OUT IF hr = 0 BEGIN SELECT output = ' Source: ' + source PRINT output SELECT output = ' Description: ' + description PRINT output INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Sub

39、ject, Body, iMsg, hr, source, description, output, 'sp_OAGetErrorInfo for sp_OASetProperty From') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END EXEC hr = sp_OASetProperty iMsg, 'Subject', Subject IF hr <

40、>0 BEGIN SELECT hr INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, description, output, 'Failed at sp_OASetProperty Subject') EXEC hr = sp_OAGetErrorInfo NULL, source OUT, description OUT IF hr = 0 BEGIN SELECT output = ' Source:

41、 ' + source PRINT output SELECT output = ' Description: ' + description PRINT output INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, description, output, 'sp_OAGetErrorInfo for sp_OASetProperty Subject') GOTO send_cdosysmail

42、_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. EXEC hr = sp_OASetProperty iMsg, 'TextBody', Body IF hr <>0 BEGIN SELECT hr INSERT INTO dbo.cdosy

43、smail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, description, output, 'Failed at sp_OASetProperty TextBody') EXEC hr = sp_OAGetErrorInfo NULL, source OUT, description OUT IF hr = 0 BEGIN SELECT output = ' Source: ' + source PRINT output SELECT output

44、 = ' Description: ' + description PRINT output INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, description, output, 'sp_OAGetErrorInfo for sp_OASetProperty TextBody') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OA

45、GetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END EXEC hr = sp_OAMethod iMsg, 'Send', NULL IF hr <>0 BEGIN SELECT hr INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, description, output, 'Failed at sp_OAMethod Send

46、') EXEC hr = sp_OAGetErrorInfo NULL, source OUT, description OUT IF hr = 0 BEGIN SELECT output = ' Source: ' + source PRINT output SELECT output = ' Description: ' + description PRINT output INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMs

47、g, hr, source, description, output, 'sp_OAGetErrorInfo for sp_OAMethod Send') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END- Do some error handling after each step if you have to.- Clean up the objects created. sen

48、d_cdosysmail_cleanup:If (iMsg IS NOT NULL) - if iMsg is NOT NULL then destroy itBEGINEXEC hr=sp_OADestroy iMsg- handle the failure of the destroy if neededIF hr <>0 BEGINselect hr INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, descriptio

49、n, output, 'Failed at sp_OADestroy') EXEC hr = sp_OAGetErrorInfo NULL, source OUT, description OUT- if sp_OAGetErrorInfo was successful, print errorsIF hr = 0BEGINSELECT output = ' Source: ' + source PRINT output SELECT output = ' Description: ' + description PRINT outputINSERT INTO dbo.cdosysmail_failures VALUES (getdate(), spid, From, To, Subject, Body, iMsg, hr, source, description, out