SQL Server 發送郵件新解：透過 CDO.Message 呼叫 IIS SMTP 組件

背景痛點

傳統 SQL Server Database Mail 需配置 Exchange/Outlook，步驟繁瑣且易失敗。利用 sp_OA* 延伸預存程序建立 COM 物件 CDO.Message，直接呼叫 IIS 內建 SMTP 服務發信，無需郵件用戶端。

預存程序實作

CREATE PROCEDURE sys_sendmail
    @From varchar(100),
    @To varchar(100),
    @Bcc varchar(500),
    @Subject varchar(400) = ' ',
    @Body ntext = ' '
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @obj int, @hr int;

    -- 建立 CDO.Message 物件
    EXEC @hr = sp_OACreate 'CDO.Message', @obj OUT;
    IF @hr <> 0 GOTO ErrorHandler;

    -- 設定發送方式：2 = SMTP 網路傳送
    EXEC @hr = sp_OASetProperty @obj, 'Configuration.Fields("https://schemas.microsoft.com/cdo/configuration/sendusing").Value', '2';
    -- SMTP 伺服器位址
    EXEC @hr = sp_OASetProperty @obj, 'Configuration.Fields("https://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.163.com';

    -- SMTP 驗證（若伺服器需驗證）
    EXEC @hr = sp_OASetProperty @obj, 'Configuration.Fields("https://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value', '1'; -- Basic Auth
    EXEC @hr = sp_OASetProperty @obj, 'Configuration.Fields("https://schemas.microsoft.com/cdo/configuration/sendusername").Value', 'your_username';
    EXEC @hr = sp_OASetProperty @obj, 'Configuration.Fields("https://schemas.microsoft.com/cdo/configuration/sendpassword").Value', 'your_password';
    EXEC @hr = sp_OAMethod @obj, 'Configuration.Fields.Update', NULL;

    -- 郵件內容
    EXEC @hr = sp_OASetProperty @obj, 'To', @To;
    EXEC @hr = sp_OASetProperty @obj, 'Bcc', @Bcc;
    EXEC @hr = sp_OASetProperty @obj, 'From', @From;
    EXEC @hr = sp_OASetProperty @obj, 'Subject', @Subject;
    EXEC @hr = sp_OASetProperty @obj, 'TextBody', @Body;

    -- 發送
    EXEC @hr = sp_OAMethod @obj, 'Send', NULL;
    IF @hr <> 0 GOTO ErrorHandler;

    PRINT 'success';
    EXEC sp_OADestroy @obj;
    RETURN 0;

ErrorHandler:
    EXEC sp_OAGetErrorInfo @obj;
    EXEC sp_OADestroy @obj;
    RETURN @hr;
END;
GO

必要條件與注意事項

• 伺服器需安裝 IIS SMTP 服務並正確設定轉寄/中繼權限。
• SQL Server 服務帳戶需具備存取 CDO 元件權限（通常需在元件服務中設定 CDO.Message 的啟動與存取權限）。
• sp_OA* 屬高風險擴充程序，生產環境請嚴格限制執行權限（GRANT EXEC ON sp_OACreate TO 特定角色）。
• SQL Server 2005+ 內建 Database Mail（msdb.dbo.sp_send_dbmail）架構更穩健，優先考慮原生方案。

呼叫範例

EXEC sys_sendmail
    @From = 'alert@company.com',
    @To = 'dba@company.com',
    @Subject = '資料庫備份失敗警示',
    @Body = '請立即檢查備份作業狀態。';