在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

它返回的一些列

在第二個圖片中你可以看到，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傳送郵件的過程，並證明是可行的。故障排除錯誤和設定也都很簡單。

異常和錯誤是開發中不可避免的一部分，但處理這些問題卻是開發人員的使命挑戰。