萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 郵件功能完整指南:xp_sendmail、xp_readmail 等擴展預存程序詳解

SQL Server 提供透過 Exchange 或 Outlook 收發郵件的擴展預存程序,以下整理核心用法。

1. 啟動/停止 SQL Mail

  • 啟動:xp_startmail [@user], [@password] (參數皆可選) 或企業管理員 → Support Services → SQL Mail 右鍵 Start。
  • 停止:xp_stopmail 或企業管理員 Stop。

2. 發送郵件 (xp_sendmail)

xp_sendmail
    [@recipients =] 'recipients[;...n]'
    [,[@message =] 'message']
    [,[@query =] 'query']
    [,[@attachments =] 'attachments']
    [,[@copy_recipients =] 'copy_recipients[;...n]']
    [,[@blind_copy_recipients =] 'blind_copy_recipients[;...n]']
    [,[@subject =] 'subject']
    [,[@type =] 'type']
    [,[@attach_results =] 'attach_value']
    [,[@no_output =] 'output_value']
    [,[@no_header =] 'header_value']
    [,[@width =] width]
    [,[@separator =] 'separator']
    [,[@echo_error =] 'echo_value']
    [,[@set_user =] 'user']
    [,[@dbuse =] 'database'];

必要參數:@recipients (收件人,分號分隔)。

參數說明
@recipients收件人,分號分隔
@message郵件內文
@query執行並附加結果的查詢 (除觸發器 inserted/deleted 表外,可引用任何物件)
@attachments附件路徑
@copy_recipients副本 (CC)
@blind_copy_recipients密件副本 (BCC)
@subject主旨
@attach_results查詢結果作為附件發送
@no_header不發送查詢結果欄位名
@set_user查詢連接用戶,預設 Guest
@dbuse查詢所用資料庫,預設預設資料庫

3. 讀取收件箱郵件 (xp_readmail)

xp_readmail
    [[@msg_id =] 'message_number']
    [,[@type =] 'type' [OUTPUT]]
    [,[@peek =] 'peek']
    [,[@suppress_attach =] 'suppress_attach']
    [,[@originator =] 'sender' OUTPUT]
    [,[@subject =] 'subject' OUTPUT]
    [,[@message =] 'message' OUTPUT]
    [,[@recipients =] 'recipients[;...n]' OUTPUT]
    [,[@cc_list =] 'copy_recipients[;...n]' OUTPUT]
    [,[@bcc_list =] 'blind_copy_recipients[;...n]' OUTPUT]
    [,[@date_received =] 'date' OUTPUT]
    [,[@unread =] 'unread_value' OUTPUT]
    [,[@attachments =] 'attachments[;...n]' OUTPUT]
    [,[@skip_bytes =] bytes_to_skip OUTPUT]
    [,[@msg_length =] length_in_bytes OUTPUT]
    [,[@originator_address =] 'sender_address' OUTPUT];

關鍵輸出參數:@originator (寄件人)、@subject、@message、@skip_bytes (分段讀取長郵件)、@msg_length (總長度)。

4. 依序處理下一封郵件 (xp_findnextmsg)

xp_findnextmsg [[@msg_id =] 'message_number' [OUTPUT]] [,[@type =] type] [,[@unread_only =] 'unread_value'];

5. 刪除郵件 (xp_deletemail)

xp_deletemail {'message_number'};

不指定編號則刪除收件箱所有郵件。

6. 自動處理郵件 (sp_processmail)

sp_processmail
    [[@subject =] 'subject']
    [,[@filetype =] 'filetype']
    [,[@separator =] 'separator']
    [,[@set_user =] 'user']
    [,[@dbuse =] 'dbname'];
本文轉載自 ASP中華網,轉載請註明出處。
copyright © 萬盛學電腦網 all rights reserved