在SQL Server中使用存储过程发送电​​子邮件

本文由码农网 – 小峰原创翻译,转载请看清文末的转载要求,欢迎参与我们的付费投稿计划

简介

这是一个很有意思的讨论话题。现在我们习惯把邮件集成到每一个应用程序中。我们使用SMTP设置在.NET的Web.Config中整合电子邮件,使用Send方法来发送邮件。最近,我遇到了一个有趣的挑战,即如何从SQL Server发送电子邮件。假设我们不得不跟踪成功的有计划的SQL查询执行。我们不能为了检查它是否成功而每次去修改table。如果我们能得到某种形式的通知,来帮助我们知道执行的状态,那就好了。是的,利用预定义的几个存储过程从SQL Server发送邮件,这是可能的。

一起来学学吧。

开始

我们的目的是使用预定义的存储过程来发送邮件。首先,我们需要建立一个账户——这是服务器发送邮件所需的认证信息。一般邮件是通过SMTP(Simple Mail Transfer Protocol)发送的。这些设置将取决于服务器应用程序的需求。请记住配置必须是有效的。

创建一个数据库帐号:

EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = 'SendEmailSqlDemoAccount'
  , @description = 'Sending SMTP mails to users'
  , @email_address = 'suraj.0241@gmail.com'
  , @display_name = 'Suraj Sahoo'
  , @replyto_address = 'suraj.0241@gmail.com'
  , @mailserver_name = 'smtp.gmail.com'
  , @port = 587
  , @username = 'XXXXXX'
  , @password = 'XXXXXX'
Go

请使用正确的认证信息和服务器设置,以便成功地发送邮件,否则邮件就会发送失败,被阻塞在发送队列中。

下一步是创建将用于设置数据库邮件的profile(配置文件)。请看下面:

EXEC msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'SendEmailSqlDemoProfile'
  , @description = 'Mail Profile description'
Go

Profile用于设置邮件配置和邮件发送。

下一步骤是将帐户映射到profile。这是让profile知道,它需要用哪个帐户的认证信息来确保发送成功。

-- 添加帐户到配置文件
EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'SendEmailSqlDemo'
  , @account_name = 'SendEmailSql'
  , @sequence_number = 1
GO

这样,我们就能成功发送电子邮件了。邮件发送查找片段如下所示:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SendEmailSqlDemo2'
  , @recipients = 'suraj.0241@gmail.com'
  , @subject = 'Automated Test Results (Successful)'
  , @body = 'The stored procedure finished successfully.'
  , @importance ='HIGH' 
GO

有时候使用存储过程,并不能得到执行。因此,可以尝试catch块,以及Begin和End处理在一些存储过程中是强制性的。

举个例子,假设我们有一个使用存储过程的SELECT INSERT查询,那么会发生的事情是,我们需要从4个table中选择并插入,这4个table即Users | UserLogin | UserEmployment | Departments

对于每一个新屏幕的创建,我们要操纵和选择用户,根据外键,再次插入到具有不同外键的相同table中,代表特定的屏幕。查询如下:

BEGIN TRY
  BEGIN TRAN
 INSERT INTO
   dbo.[User]
 SELECT
    us.UserName,
	us.UserAddress,
	us.UserPhone,
    @fkScreenID
 FROM
   dbo.[User] as us
 WHERE
   UserID= @userID
 COMMIT TRAN
    END TRY
   BEGIN CATCH
  ROLLBACK TRAN
  END
  END CATCH  //其他table的代码与此类似。添加Try Catch到整个SP执行块(Executing Block)会更好

这里的事件要是失败的话,会转移到Catch块,在Catch块中我们可以让电子邮件一直发送程序以获取相关成功或失败的通知和原因,以及告知哪里失败。这对开发人员非常有帮助。

故障排除邮件

还有一些存储过程能让我们知道邮件是成功的,失败的还是尚在排队中。这真是一个超棒的功能。

要检查邮件是否已经成功发送和发布,我们可以运行以下查询:

select * from msdb.dbo.sysmail_sentitems

它返回的一些列

Email1

在第二个图片中你可以看到,sent_status属性为sent,这表明邮件已成功发送。

为检查可能无法发送的未发送邮件,我们运行以下查询:

select * from msdb.dbo.sysmail_unsentitems

为检查甚至不能重新从队列中发送的失败邮件,我们运行下面的查询: -

select * from msdb.dbo.sysmail_faileditems

有关故障及原因的详细信息,故障查找查询将如下所示:

SELECT items.subject,
    items.last_mod_date
    ,l.description FROM msdb.dbo.sysmail_faileditems as items
INNER JOIN msdb.dbo.sysmail_event_log AS l
    ON items.mailitem_id = l.mailitem_id
GO

结果类似于:

Email3

上面的错误描述为“No Such Host”错误。该错误通常发生在有一些SMTP服务器连接设置错了的时候。我们需要靠自己排除故障——重新检查设置认证信息,然后再试试。如果依然不能工作,那么就需要检查DNS服务器设置,再次重试配置。

结论

这一次我们讨论了如何使用存储过程从我们自己的SQL发送邮件的过程,并证明是可行的。故障排除错误和设置也都很简单。

异常和错误是开发中不可避免的一部分,但处理这些问题却是开发人员的使命挑战。

译文链接:http://www.codeceo.com/article/sql-server-send-mail.html
英文原文:Sending Email Using Stored Procedures in Sql Server
翻译作者:码农网 – 小峰
转载必须在正文中标注并保留原文链接、译文链接和译者等信息。]

相关文章

在文章中找不到问题答案?您还可以

前往问答社区提问

关注我们的微博

付费投稿计划
点击查看详情