【Vegas原创】SQL Server调用CDO发送邮件

标签：sp EXEC hr CDO Server iMsg -- Vegas mail
•用于某事件过期mail提醒。 

操作步骤：   1.新建DailyJob,事件中填写： 
       exec dbo.EIP_DeadLine_Notice 
/*********************************************************************

*             Vegas Lee  制作       2006.01.17                       *

**********************************************************************/ 
CREATE  PROCEDURE dbo.EIP_DeadLine_Notice AS
DECLARE  @mailTitle nvarchar(100), @mailBody nvarchar(4000), @theMailList nvarchar(500),@C_mailBody nvarchar(4000)

-- 项目到期时，系统自动发mail提醒==========================================
DECLARE Cur_EIP_DeadLine  CURSOR FOR
select t.Unique_ID,m1.name as From_Name,t.Category,t.Name,t.DeadLine,t.Description,t.people,t.finish,m.mail,m1.mail as from_mail from todo t,members m,members m1 where datediff(dd,t.DeadLine,getdate())<1 and t.finish=0 and t.people=m.Account_ID and m1.Account_ID=t.User_Account

Set @C_mailBody=N'<HTML>...'
Set @C_mailBody= @C_mailBody +'<tr>...'
DECLARE @v_Unique_ID INT,@v_From_Name VARCHAR(10),@v_Category VARCHAR(10) 
DECLARE @v_Name VARCHAR(50),@v_DeadLine nvarchar(20),@v_Description VARCHAR(1000),@v_people nvarchar(10),@v_finish INT,@v_email VARCHAR(100),@v_from_mail VARCHAR(100)


OPEN  Cur_EIP_DeadLine
FETCH Cur_EIP_DeadLine
INTO @v_Unique_ID,@v_From_Name,@v_Category,@v_Name,@v_DeadLine,@v_Description,@v_people,@v_finish,@v_email,@v_from_mail

WHILE (@@FETCH_STATUS = 0)
       BEGIN
    Set @mailTitle='系统通知：' 
    Set @mailBody ='<HTML>..' 
    
Set @C_mailBody= @C_mailBody + '...'    
    SET @theMailList = rtrim(@v_email)
    
    
    EXEC sp_send_cdosysmail @v_from_mail,@theMailList,@mailTitle,@mailBody
    

FETCH Cur_EIP_DeadLine INTO @v_Unique_ID,@v_From_Name,@v_Category,@v_Name,@v_DeadLine,@v_Description,@v_people,@v_finish,@v_email,@v_from_mail
        END
    
CLOSE Cur_EIP_DeadLine
DEALLOCATE Cur_EIP_DeadLine

-----=====================通知
Set @C_mailBody=@C_mailBody + '</table></body></html>'

SET @theMailList = @v_from_Mail
Exec sp_send_cdosysmail_gb @v_from_Mail,@theMailList,@mailTitle,@C_mailBody

GO  3.  sp_send_cdosysmail_gb:

CREATE PROCEDURE [dbo].[sp_send_cdosysmail_gb] 
   @From varchar(100) ,
   @To varchar(300) ,
   @Subject varchar(100)=" ",
   @Body nvarchar(4000) =" "
   --@Body ntext
/*********************************************************************

This stored procedure takes the above parameters and sends an e-mail. 
All of the mail configurations are hard-coded in the stored procedure. 
Comments are added to the stored procedure where necessary.
Reference 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
***********************************************************************/ 
   AS
   Declare @iMsg int
   Declare @hr int
   Declare @source varchar(255)
   Declare @description varchar(500)
   Declare @output varchar(1000)

--************* 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', '[Mailserver]' 

-- 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'.
   EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
   EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBodyPart.Charset','gb2312' 
   EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- 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


GO 
sp_send_cdosysmail(big5)CREATE PROCEDURE [dbo].[sp_send_cdosysmail] 
   @From varchar(100) ,
   @To varchar(300) ,
   @Subject varchar(100)=" ",
   @Body nvarchar(4000) =" "
   --@Body ntext
/*********************************************************************

This stored procedure takes the above parameters and sends an e-mail. 
All of the mail configurations are hard-coded in the stored procedure. 
Comments are added to the stored procedure where necessary.
Reference 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
***********************************************************************/ 
   AS
   Declare @iMsg int
   Declare @hr int
   Declare @source varchar(255)
   Declare @description varchar(500)
   Declare @output varchar(1000)

--************* 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', '[MailServer]'

-- 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'.
   EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
   --EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBodyPart.Charset','utf8' 
   EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- 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

GO
 4.查询ole automation procedures.txt 
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO


---查询状态 
EXEC sp_configure 'Ole Automation Procedures';
GO
标签：sp,EXEC,hr,CDO,Server,iMsg,--,Vegas,mail
From： https://blog.51cto.com/amadeus/5751663
【Vegas原创】SQL Server调用CDO发送邮件

相关文章

赞助商

阅读排行
