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

引言

这是一个非常有趣的讨论话题。现在，每个应用程序都集成了邮件功能。我们在 .NET 中使用 Web.Config 中的 SMTP 设置集成电子邮件，并使用 Send 方法发送邮件。最近，我遇到了一个有趣的挑战，我们需要从 SQL Server 发送电子邮件。假设我们需要跟踪计划 SQL 查询的成功执行情况。我们不能每次都查看它修改的表，以检查它是否真的成功运行。如果我们能收到某种通知，帮助我们了解执行状态，那就太好了。是的，可以使用一些预定义的存储过程从 SQL Server 发送邮件。
让我们学习如何操作：

入门

请记住，我们将使用预定义的存储过程来发送邮件。首先，我们需要设置一个帐户，其中包含服务器发送邮件所需的凭据。通常，邮件通过 SMTP（简单邮件传输协议）发送。设置将取决于应用程序所需的服务器。请记住，配置需要有效。

创建数据库帐户

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

请使用正确的凭据和服务器设置，以便成功传递邮件，否则它们将失败并排队。

下一步是创建一个配置文件，用于配置数据库邮件。存储过程如下所示：

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

此配置文件将用于设置邮件配置和发送电子邮件。

下一步是将帐户映射到配置文件。这将让配置文件知道它需要使用哪个帐户凭据才能成功发送。
如下所示：

-- Add the account to the 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

所使用的存储过程有时容易无法执行。因此，在某些存储过程中，Try catch 块以及 Begin 和 End 事务是强制性的。
让我们举个例子。

假设我们有一个使用存储过程的 SELECT INSERT 查询，因此发生的情况是我们正在从 4 个表中选择和插入，例如：
Users | UserLogin | UserEmployment | Departments

对于每个新屏幕的创建，我们都在根据其 PK 操作和选择用户，然后使用不同的 FK 再次插入到相同的表中，表示特定的屏幕。查询如下所示：

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  //Similarly for other tables as well we continue. 
             //It is better to add the Try Catch to whole SP Executing Block

在这里，如果事务失败，它将移动到 Catch 块中，在那里，我们可以有电子邮件发送过程，以便获得有关成功或失败以及原因和失败位置的通知。这对任何开发人员都非常有帮助。

邮件故障排除

还有一些存储过程可以让我们知道邮件是否成功、失败或保留在队列中。这是一个很棒的功能。 :).

要检查已成功发送和传递的邮件，我们运行以下查询：

select * from msdb.dbo.sysmail_sentitems

它返回的一些列是：

在第二张图片中，您可以看到我们的 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

结果如下所示：

上面的错误描述类似于“No Such Host”错误。当我们的一些 SMTP 服务器连接设置错误时，通常会出现此错误。我们需要自行排除故障并重新检查设置凭据，然后重试。如果仍然无法正常工作，我们需要查找 DNS 服务器设置并使用该配置再次重试。不过不用担心。 :)

结论

因此，我们在这里讨论了如何使用存储过程从我们自己的 SQL 发送邮件，以及它们如何被证明是有帮助的。故障排除错误在这里非常容易，设置也是如此。

异常和错误是开发的一部分，无法避免，但处理它们是一项挑战，开发人员可以轻松地做到这一点。

参考文献

• 电子邮件架构师
• MSDN

历史

• 2015 年 9 月 8 日：初始版本

CodeProject