【Vegas原创】SQL Server调用CDO发送邮件
•用于某事件过期mail提醒。
操作步骤：
1.新建DailyJob,事件中填写：
exec dbo.EIP_DeadLine_Notice
2.
/*********************************************************************
* 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
/*********************************************************************
* 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
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
喜欢请赞赏一下啦^_^
