您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2008

SQL Server 2008 Database Mail 自動發送郵件完整實作指南

編輯：SQL Server 2008

前言

SQL Server 內建的 Database Mail 功能提供了強大的郵件發送能力，結合 Agent Job 可實現全自動化的郵件通知機制。本文詳細記錄從環境建置、帳號設定、範本設計到自動化排程的完整實作流程。

Database Mail 環境建置

兩種設定方式

圖形介面：透過 SSMS 的「設定 Database Mail」精靈逐步完成
T-SQL 腳本：適用於自動化部署與版本控管

核心概念：Profile 與 Account

Account 定義 SMTP 伺服器連線參數（伺服器名稱、連接埠、認證資訊）；Profile 是 Account 的邏輯群組，提供彈性的發送策略。注意：SMTP 伺服器名稱必須是真實存在且可用的郵件伺服器，非任意自訂名稱。

移除既有設定腳本

DECLARE @account_name sysname, @profile_name sysname;
SELECT @account_name = N'want_to_delete_Account', @profile_name = N'want_to_delete_Profile';

-- 從 Profile 移除 Account
EXEC msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = @profile_name, @account_name = @account_name;
-- 刪除 Account
EXEC msdb.dbo.sysmail_delete_account_sp @account_name = @account_name;
-- 刪除 Profile
EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = @profile_name;

郵件內容範本設計

資料表結構

建立兩張資料表支援動態郵件內容組裝：

資料表	主要欄位	說明
AutoSendEmail	UserID, EmailAddress, EmailTextID	待發送郵件佇列，記錄收件者與範本關聯
EmailText	EmailTextID, Subject, TitleName, Object, Conclusion, Inscribe	靜態範本表，支援多種郵件格式

範本資料範例

INSERT INTO EmailText (EmailTextID, Subject, TitleName, Object, Conclusion, Inscribe) 
VALUES (1, '註冊成功', '尊敬的<UserName>:',
    '<br>感謝您申請註冊某某公司會員! 您的註冊帳號為<LoginName>，密碼為<Password>，請妥善保管您的密碼！',
    '<br>感謝您對我們公司的支持！若有任何意見或建議，歡迎來電諮詢:123-456789。請勿回覆此郵件！',
    ' 某某公司');

採用欄位拆分設計而非單一 Body 欄位，主要考量：

格式控制更靈活（HTML 標籤集中管理）
不同郵件類型保持統一結構，利於維護
動態參數替換邏輯清晰

核心預存程序：動態組裝與發送

CREATE PROCEDURE [Services].[SendRegisteredEmail]
(
    @UserID int,
    @EmailAddress nvarchar(100)
)
AS
BEGIN
    DECLARE @userName nvarchar(30), @Login nvarchar(50), @password nvarchar(50);
    DECLARE @titleName nvarchar(100), @titleResult nvarchar(100);
    DECLARE @object nvarchar(600), @objectResult nvarchar(600);
    DECLARE @Subject1 nvarchar(100), @Conclusion nvarchar(600), @Inscrible nvarchar(100);
    DECLARE @result int, @body1 nvarchar(max);

    -- 取得使用者姓名
    SELECT @userName = LastName FROM [Users].[User] WHERE UserID = @UserID;
    
    -- 取得郵件範本內容
    SELECT @Subject1 = Subject, @titleName = TitleName, @object = Object, 
           @Conclusion = Conclusion, @Inscrible = Inscribe 
    FROM [Services].[EmailText] WHERE EmailTypeID = 1;
    
    -- 替換稱呼變數
    SELECT @titleResult = REPLACE(@titleName, '<UserName>', @userName);
    
    -- 取得帳號密碼
    SELECT @Login = Login, @password = Password FROM [Users].[RegisteredUser] WHERE UserID = @UserID;
    
    -- 替換內容變數
    SELECT @objectResult = REPLACE(@object, '<LoginName>', @Login);
    SELECT @objectResult = REPLACE(@objectResult, '<Password>', @password);
    
    -- 組裝完整 HTML 郵件內容
    SET @body1 = @titleResult + N'<br>' + @objectResult + N'<br>' + @Conclusion + N'<br>' + @Inscrible;
    
    -- 發送郵件
    EXEC @result = msdb.dbo.sp_send_dbmail 
        @profile_name = 'SurmountGroupProfile',
        @recipients = @EmailAddress,
        @subject = @Subject1,
        @body = @body1,
        @body_format = 'HTML';
    
    RETURN @result; -- 0 成功，1 失敗
END

關鍵函數：REPLACE 字串替換

語法：REPLACE(string_expression1, string_expression2, string_expression3)
將字串 1 中所有出現的字串 2 替換為字串 3。本範例用於將範本佔位符（如 <UserName>）替換為實際資料。

Agent Job 自動化排程

Job Step 腳本

DECLARE @recordID int, @userID int, @email nvarchar(100), @typeID int, @result int;
DECLARE EmailCursor CURSOR FOR 
    SELECT RecordID, UserID, EmailAddress, EmailTextID FROM AutoSendEmail;

OPEN EmailCursor;
FETCH NEXT FROM EmailCursor INTO @recordID, @userID, @email, @typeID;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    IF (@typeID = 1)
    BEGIN
        EXEC @result = SendRegisteredEmail @userID, @email;
        IF (@result = 0) -- 發送成功
        BEGIN
            DELETE FROM [Services].[AutoSendEmail] WHERE RecordID = @recordID;
        END
        -- 失敗則保留，下次重試
    END
    FETCH NEXT FROM EmailCursor INTO @recordID, @userID, @email, @typeID;
END

CLOSE EmailCursor;
DEALLOCATE EmailCursor;