SQL Server：发送电子邮件 [英] SQL Server : send email

问题描述

我想使用SQL Server发送电子邮件，并从互联网找到两种可能的方式：

1. 使用AOSMTP.Mail

    创建PROCEDURE [dbo]。[RC_SendEmail] @ServerAddr varchar（80），
    @FromAddr varchar（80） ，
    @Recipient varchar（80），
    @Subject varchar（132），
    @BodyText varchar（2000）
    
    AS 
    DECLARE @hr int 
    DECLARE @oSmtp int 
    DECLARE @nRet int 
    
    EXEC @hr = sp_OACreate'AOSMTP.Mail'，@ oSmtp OUT 
    EXEC @hr = sp_OASetProperty @oSmtp， 'RegisterKey'，'用这个键替换这个文本'
    EXEC @hr = sp_OASetProperty @oSmtp，'ServerAddr'，@ServerAddr 
    EXEC @hr = sp_OASetProperty @oSmtp，'FromAddr'，@FromAddr 
    EXEC @hr = sp_OAMethod @oSmtp，'AddRecipient'，NULL，@Recipient，@Recipient，0 
    EXEC @hr = sp_OASetProperty @oSmtp，'Subject'，@Subject 
    EXEC @hr = sp_OASetProperty @oSmtp，'BodyText'，@BodyText 
    
    EXEC @hr = sp_OAMethod @oSmtp，'SendMail'，@nRet OUT 
    EXEC @hr = sp_OADestroy @oSmtp 
     

但是，此方法需要服务器地址。

我应该输入什么地址？电子邮件服务器？还是数据库服务器？
我只看到它在互联网上使用'localhost'。但是这对我来说不起作用。

1. 使用CDONTS.NewMail

   
   
   pre> CREATE PROCEDURE [dbo]。[SendEmail]
@From varchar（100），
@To varchar（100），
@Subject varchar （100），
@Body varchar（4000），
@CC varchar（100）= null，
@BCC varchar（100）= null
AS
声明@MailID int
声明@hr int
声明@result int

EXEC @hr = sp_OACreate'CDONTS.NewMail'，@MailID OUT
EXEC @hr = sp_OASetProperty @MailID，'From'，@ From
EXEC @hr = sp_OASetProperty @MailID，'Body'，@Body
EXEC @hr = sp_OASetProperty @MailID，'BCC'，@ BCC
EXEC @hr = sp_OASetProperty @MailID，'CC'，@CC
EXEC @hr = sp_OASetProperty @MailID，'Subject'，@Subject
EXEC @hr = sp_OASetProperty @MailID，'To'，@To
EXEC @hr = sp_OAMethod @MailID，'Send'，@result OUT
EXEC @hr = sp_OADestroy @MailID
select @resu


这个不需要服务器地址

任何人都可以解释更多关于这些和我应该做什么的细节？

非常感谢!!!

解决方案

忘记使用SQL Server上内置的发送邮件功能。它不会变得容易。

通过向导配置它，您可以简单地在proc中执行此操作，如链接的教程所示：

  USE msdb 
 GO 
 EXEC sp_send_dbmail @ profile_name ='PinalProfile'，
 @ recipients ='test @ Example.com'，
 @ subject ='测试消息'，
 @ body ='这是测试消息的正文'
  

I want to use SQL Server to send email and found two possible way from the internet:

1. Using AOSMTP.Mail

   Create PROCEDURE [dbo].[RC_SendEmail]  @ServerAddr varchar(80),
      @FromAddr varchar(80), 
      @Recipient varchar(80), 
      @Subject varchar(132),
      @BodyText varchar(2000)
   
   AS
     DECLARE @hr int
     DECLARE @oSmtp int
     DECLARE @nRet int
   
     EXEC @hr = sp_OACreate 'AOSMTP.Mail',@oSmtp OUT
     EXEC @hr = sp_OASetProperty @oSmtp, 'RegisterKey', 'replace this text by your key'
     EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', @ServerAddr
     EXEC @hr = sp_OASetProperty @oSmtp, 'FromAddr', @FromAddr
     EXEC @hr = sp_OAMethod @oSmtp, 'AddRecipient', NULL, @Recipient, @Recipient, 0 
     EXEC @hr = sp_OASetProperty @oSmtp, 'Subject', @Subject 
     EXEC @hr = sp_OASetProperty @oSmtp, 'BodyText', @BodyText 
   
     EXEC @hr = sp_OAMethod @oSmtp, 'SendMail', @nRet OUT 
     EXEC @hr = sp_OADestroy @oSmtp
   

However, this method required a Server Address.

What is the address I am supposed to enter? the email server? or the database server? I only see that it use 'localhost' in the internet. But it doesn't work for me.

1. Using CDONTS.NewMail

   CREATE PROCEDURE [dbo].[SendEmail] 
       @From varchar(100),
       @To varchar(100),
       @Subject varchar(100),
       @Body varchar(4000),
       @CC varchar(100) = null,
       @BCC varchar(100) = null
   AS
       Declare @MailID int
       Declare @hr int
       Declare @result int
   
       EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
       EXEC @hr = sp_OASetProperty @MailID, 'From',@From
       EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
       EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
       EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
       EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
       EXEC @hr = sp_OASetProperty @MailID, 'To', @To
       EXEC @hr = sp_OAMethod @MailID, 'Send', @result OUT
       EXEC @hr = sp_OADestroy @MailID
       select @result
   

This one required no Server Address but it doesn't work as well.

Can anyone explain in more details about these and what I should do?

Thank you very much!!!

解决方案

Forget that, use the send email capabilities built-in on SQL Server. It doesn't get any easier.

Once you configure it through the wizard, you can simply do this inside your procs, as shown in the tutorial linked:

USE msdb
GO
EXEC sp_send_dbmail @profile_name='PinalProfile',
@recipients='[e-mail]',
@subject='Test message',
@body='This is the body of the test message.'

这篇关于SQL Server：发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！