SQL Mail 自動回覆：接收查詢郵件並返回執行結果附件

功能簡介

SQL Mail 可收發郵件。本方案實現：郵件伺服器收到郵件後，解析郵件內容（需為合法 SELECT 查詢語句），執行該查詢，將結果集以附件形式自動回覆給寄件者。

範例：寄件內容為 select top 10 id, name from sysobjects，系統自動回覆附件包含查詢結果。

前置需求

1. 已正確設定 SQL Mail 服務（設定 Outlook/Exchange Profile、啟用 SQLAgentMail 等）。
2. 具備 master 資料庫 xp_findnextmsg、xp_readmail、xp_sendmail 等擴充預存程序權限。

核心邏輯：修改 sp_processmail

原始 sp_processmail 需稍作調整，關鍵在於解析寄件者真實郵件地址（@originator_address），並將查詢結果以附件寄回。以下為完整修改腳本：

ALTER PROCEDURE sp_processmail
    @subject VARCHAR(255) = NULL,
    @filetype VARCHAR(3) = 'txt',
    @separator VARCHAR(3) = 'tab',
    @set_user VARCHAR(132) = 'guest',
    @dbuse VARCHAR(132) = 'master'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @status INT,
            @msg_id VARCHAR(64),
            @originator VARCHAR(255),
            @originator_address VARCHAR(255),
            @cc_list VARCHAR(255),
            @msgsubject VARCHAR(255),
            @query VARCHAR(8000),
            @messages INT,
            @mapifailure INT,
            @resultmsg VARCHAR(80),
            @filename VARCHAR(12),
            @current_msg VARCHAR(64),
            @attach_path VARCHAR(260);

    SELECT @messages = 0, @mapifailure = 0;

    IF @separator = 'tab' SELECT @separator = CHAR(9);

    -- 取得第一封未讀郵件
    EXEC @status = master.dbo.xp_findnextmsg @msg_id = @msg_id OUTPUT, @unread_only = 'true';
    IF @status <> 0 SELECT @mapifailure = 1;

    WHILE (@mapifailure = 0)
    BEGIN
        IF @msg_id IS NULL OR @msg_id = '' BREAK;

        -- 讀取郵件內容（僅預覽，不標記為已讀）
        EXEC @status = master.dbo.xp_readmail
            @msg_id = @msg_id,
            @originator = @originator OUTPUT,
            @cc_list = @cc_list OUTPUT,
            @subject = @msgsubject OUTPUT,
            @message = @query OUTPUT,
            @peek = 'true',
            @originator_address = @originator_address OUTPUT,
            @suppress_attach = 'true';

        IF @status <> 0
        BEGIN
            SELECT @mapifailure = 1;
            BREAK;
        END

        -- 先取得下一封郵件 ID，再處理當前郵件
        SELECT @current_msg = @msg_id;
        EXEC @status = master.dbo.xp_findnextmsg @msg_id = @msg_id OUTPUT, @unread_only = 'true';
        IF @status <> 0 SELECT @mapifailure = 1;

        -- 執行查詢並產生附件
        BEGIN TRY
            -- 這裡簡化：實際需將 @query 結果寫入暫存檔，再用 xp_sendmail 寄出
            -- 範例略過檔案 I/O 細節，重點在流程控制
            EXEC master.dbo.xp_sendmail
                @recipients = @originator_address,
                @subject = 'Re: ' + ISNULL(@msgsubject, 'Query Result'),
                @message = '查詢結果請見附件。',
                @query = @query,
                @attach_results = 'true',
                @width = 255,
                @separator = @separator,
                @filetype = @filetype;
        END TRY
        BEGIN CATCH
            -- 錯誤處理：記錄或通知管理員
        END CATCH

        -- 標記當前郵件為已讀/刪除（視需求）
        -- EXEC master.dbo.xp_deletemail @msg_id = @current_msg;
    END
END
GO

排程執行

建立 SQL Server Agent 作業，每分鐘執行一次 EXEC sp_processmail，即可實現近乎即時的自動回覆。

安全性建議

• 限制寄件者網域或郵件地址白名單。
• 對 @query 進行關鍵字過濾，僅允許 SELECT，防止注入。
• 設定查詢逾時，避免長時間運行阻塞郵件處理。