发送邮件的存储过程

 为了实现对数据库的监控报警，需要在监控数据库的脚本中调用发送邮件的功能，因此开发了这个存储过程。

 

 这个存储过程可以实现：

 1, 自定义发件人；
 2,发送给多个收件人；
 3,发送附件；
 4,邮件格式可以为文本TXT，或者HTML；

 

 说明：

 1,这个存储过程要成功执行，需要指定 “@smtpserver smtp服务器”
 2,需要启用Ole Automation Procedures：

sp_configure 'Ole Automation Procedures', 1 
RECONFIGURE WITH OVERRIDE 
GO

 

 

下面是存储过程代码：

USE Master GO if object_id('dbo.sp_send_mail') is not null drop procedure [dbo].[sp_send_mail] GO CREATE PROCEDURE [dbo].[sp_send_mail] @From varchar(256) , --发件人 @To varchar(1000) , --收件人,split by semicolon,such as: dba@xxx.com;dba@xxx.com @Subject varchar(256)='', --标题 @Body varchar(8000) ='' , --正文 @attachments varchar(1000)='', --one or more than one attachment,split by semicolon,such as: c:/t1.txt;c:/t2.csv @TXT_HTML char(10)='Textbody' --HTML e-mail, use 'HTMLBody' ;Text e-mail ,use 'TextBody'. with encryption AS /********************** {VER EDITOR DATE COMMENT } {ver 1.0 summer.yang 2005-06-30 create procedure } REMARK: 1.References to the CDOSYS objects are at the following MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp EXAMPLE: exec sp_send_mail @From='dba@xxx.com', @To ='dba@xxx.com;' , @Subject='Test from sp_send_mail', @Body ='Test the email', @attachments ='', @TXT_HTML='Textbody' ************************/ set nocount on Declare @iMsg int,@hr int,@source varchar(255),@description varchar(500) , @output varchar(1000),@message varchar(8000) ------ declare @smtpserver varchar(200),@sendusername varchar(200) ,@sendpassword varchar(200) --please set the values before excute the stored procedure--@@@@@@@@ set @smtpserver = '' --smtp服务器 if @sendusername='' or @sendpassword='' begin raiserror 50000 'please set the @sendusername and @sendpassword values before excute the stored procedure' return -1 end --replace the quotation marks set @Subject=replace(@Subject,'''','''''') set @Body=replace(@Body,'''','''''') --************* 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 -- EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1' --EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', @sendusername --EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', @sendpassword -- 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 EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. if @TXT_HTML like 'h%' set @TXT_HTML='HTMLBody' else set @TXT_HTML='TextBody' EXEC @hr = sp_OASetProperty @iMsg, @TXT_HTML, @Body --add attachment(s) IF len(@attachments)>0 BEGIN Declare @files table(fileid int identity(1,1),[file] varchar(255)) Declare @file varchar(255),@file_exists int Declare @filecount int ; set @filecount=0 Declare @counter int ; set @counter = 1 INSERT @files SELECT unit FROM dbo.fn_Split_String(@attachments,';') SELECT @filecount=@@ROWCOUNT --loop add attachments WHILE @counter<=@filecount BEGIN SELECT @file = [file] FROM @files WHERE fileid=@counter --check the file whether exists EXEC master..xp_fileexist @file, @file_exists OUTPUT if @file_exists=1 --the file exists EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',NULL, @file else begin set @message='<sp_send_mail> Error: The attachment file does not exist! PATH='+@file+char(10) raiserror 53000 @message goto finish end --add the loop value SET @counter=@counter+1 END END --send email EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL if @@error<>0 or @hr<>0 begin raiserror 55000 '<sp_send_mail> Error: send mail failed.' end else begin print 'Success: send mail ok.' end EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = '<sp_send_mail> Error Source: ' + @source PRINT @output SELECT @output = '<sp_send_mail> Error Description: ' + @description PRINT @output END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' goto finish END -- Do some error handling after each step if you have to. -- Clean up the objects created. finish: EXEC @hr = sp_OADestroy @iMsg ------- GO