sqlserver資料庫發送HTML類型郵件

最後更新：2018-12-06 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

/*SQL Server 2000 使用通過,使用系統CDOSYS發送HTML類型郵件This script use CDOSys to send a mail. No mapi profile required.*/Use NorthwindSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE PROCEDURE [dbo].[sp_send_MailHtml]   @SmtpServer varchar(128),   @From varchar(128),   @To varchar(128),   @Cc varchar(128),   @BCc varchar(128),   @Subject varchar(124)=" ",   @Query varchar(4000) = " " /*** *   Date:         March 2008 *   Author:       daniel.eyer@free.fr *   Project:      Just for fun! *   Location:     Any database *   Permissions:  PUBLIC EXECUTE *    *   Description:  Send query result as HTML Mail  *    *    ***/ AS--Mail declaration   Declare @iMsg int   Declare @hr int   Declare @source varchar(255)   Declare @description varchar(500)   Declare @output varchar(1000)--HTML declaration   declare @Columns varchar(8000)   declare @ColHeader varchar(8000)   Declare @SqlCmd varchar(8000)   Declare @HTMLBody varchar(8000)--************* Create the CDO.Message Object ************************   EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT--***************Configuring the Message Object ******************-- This is to configure a remote SMTP server.-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp   EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'-- This is to configure the Server Name or IP address. -- Replace MailServerName by the name or IP of your SMTP Server.   EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @SmtpServer-- Save the configurations to the message object.   EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null-- Set the e-mail parameters.   EXEC @hr = sp_OASetProperty @iMsg, 'To', @To   EXEC @hr = sp_OASetProperty @iMsg, 'From', @From   If(@Cc Is Not Null) Exec @hr = sp_OASetProperty @iMsg, 'Cc', @Cc   If(@BCc Is Not Null) Exec @hr = sp_OASetProperty @iMsg, 'BCc', @BCc   EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.--   EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body/*************************************************************************/   -- drop temporary tables used. IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml1') DROP TABLE ##TEMPhtml1 IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml2')  DROP TABLE ##TEMPhtml2  -- prepare query set @SqlCmd = 'select * into ##tempHTML1 from (' + @Query + ') as t1' execute (@SqlCmd)  --Prepare columns details SELECT @columns =   COALESCE(@columns + ' + ''</td><td>'' + ', '') +   'RTrim(convert(varchar(100),isnull(' + column_name +','' '')))'  FROM tempdb.information_schema.columns   where table_name='##tempHTML1'   --Prepare column Header set @colHeader = '<tr bgcolor=#EDFEDF align=Left>' SELECT @colHeader = @colHeader + '<td><b>' + column_name + '</b></td>' FROM tempdb.information_schema.columns where table_name='##tempHTML1' set @colHeader=@colHeader + '</tr>'  --prepare final output set @SqlCmd =   'Select ''<tr><td>'' + ' +   @columns +   ' ''</td></tr> '' into ##tempHTML2 from ##tempHTML1 '  execute( @SqlCmd)   --set @finalhtmlout=  set @HtmlBody =  ' <html> <body><style type="text/css" media="all"> ' +  'table { margin-bottom: 2em; border-collapse: collapse } ' +  'td,th {border= 1 solid #999; padding: 0.2em 0.2em; font-size: 12;} ' +  '</style> <table width="100%"> ' +   @colHeader  select @HtmlBody = @HtmlBody + [</td></tr>]  from ##tempHTML2  set @HtmlBody = @HtmlBody + ' </table></body></htmL>' EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody',  @HtmlBody EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL   -- drop temporary tables used. IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml1') DROP TABLE ##TEMPhtml1 IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml2')  DROP TABLE ##TEMPhtml2  /*************************************************************************/-- Sample error handling./*   IF @hr <>0      select @hr     BEGIN       EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT       IF @hr = 0         BEGIN           SELECT @output = '  Source: ' + @source           PRINT  @output           SELECT @output = '  Description: ' + @description           PRINT  @output         END       ELSE         BEGIN           PRINT '  sp_OAGetErrorInfo failed.'           RETURN         END     END*/-- Do some error handling after each step if you need to.-- Clean up the objects created.   EXEC @hr = sp_OADestroy @iMsg  GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO--使用樣本:EXEC [sp_send_MailHtml] @SmtpServer = 'smtp伺服器IP', @From = '寄件者@domain.xx', @To = '接收者@domain.xx', @cc = NULL,   -- 抄送 @BCc= NULL, -- 密送 @Subject = '主題：List of USA Customers',  @Query = 'select * from dbo.Customers Where Country = ''USA'''  --Query to send--條件1:sp_configure 'Ole Automation Procedures',1RECONFIGURE

另外sqlserver2005中內建的系統預存程序 sp_send_dbmail

但使用前，必須使用資料庫郵件設定精靈、SQL Server 介面區配置器工具或 sp_configure 啟用資料庫郵件。

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More