SQL Server 提供透過 Exchange 或 Outlook 收發郵件的擴展預存程序,以下整理核心用法。
xp_startmail [@user], [@password] (參數皆可選) 或企業管理員 → Support Services → SQL Mail 右鍵 Start。xp_stopmail 或企業管理員 Stop。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 | 查詢所用資料庫,預設預設資料庫 |
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 (總長度)。
xp_findnextmsg [[@msg_id =] 'message_number' [OUTPUT]] [,[@type =] type] [,[@unread_only =] 'unread_value'];
xp_deletemail {'message_number'};
不指定編號則刪除收件箱所有郵件。
sp_processmail
[[@subject =] 'subject']
[,[@filetype =] 'filetype']
[,[@separator =] 'separator']
[,[@set_user =] 'user']
[,[@dbuse =] 'dbname'];
本文轉載自 ASP中華網,轉載請註明出處。