[SQL SERVER]使用 SQL CODE 来SEND 夹带 Attachment 的 E-Mail.

lyfatt3

SQL Server have a mail function call SQL mail. That is a MAPI base program.CDOSYS 可以SEND 给没安装MS OUTLOOK 的电脑. 这是编码:

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
   @From varchar(100) ,
   @To varchar(100) ,
   @Subject varchar(100)=" ",
   @Body varchar(4000) =" "
/*********************************************************************

This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/librar ... dosys_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/librar ... ation_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', '添上你的SMTP'

-- 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, 'TextBody', @Body
   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 have to.
-- Clean up the objects created.
   EXEC @hr = sp_OADestroy @iMsg
   go
======================================================================================

declare @Body varchar(4000)
   select @Body = 'This is a Test Message'
   exec sp_send_cdosysmail '[email protected]','[email protected]','Test of CDOSYS',@Body
======================================================================================

问题:
怎样加ATTACHMENT, MSDN 没有SQL 的确EXAMPLE. 谁能帮我看看.....

MSDN LINK REFFER.

http://msdn.microsoft.com/librar ... e_addattachment.asp


http://msdn.microsoft.com/librar ... ssage_interface.asp

[ Last edited by lyfatt3 on 18-4-2005 at 09:25 PM ]

[ 本帖最后由 lyfatt3 于 1-9-2005 03:52 PM 编辑 ]

goatstudio

不太熟悉这样的 send mail 方法, 但如果是 routine job, 可以用 DTS 的 vbscript 来达到.

lyfatt3

我已经完成测试了.
证明这个CODE可以用...

lyfatt3

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


Create   PROCEDURE [dbo].[sp_send_cdosysmail]
   @From varchar(100) ,
   @To varchar(2048) ,
   @CC varchar(2048) = '',
   @BCC varchar(2048) = '',
   @Subject varchar(100)='',
   @Body varchar(4000) ='',
   @Attachments varchar (1024)=''
/*********************************************************************

This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/librar ... dosys_messaging.asp

***********************************************************************/
   AS
   Declare @iMsg int
   Declare @hr int
   Declare @source varchar(255)
   Declare @description varchar(500)
   Declare @output varchar(1000)
   Declare @iPos int
   Declare @iMessageObjId int
   Declare @iHr int
   Declare @vcAttachment varchar(1024)
   Declare @iRtn int
   Declare @vcErrSource varchar(255)
   Declare @vcErrDescription varchar(255)

--************* 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/librar ... ation_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', 'Your SMTP Name'

-- 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, 'CC', @CC
   EXEC @hr = sp_OASetProperty @iMsg, 'BCC', @BCC
   EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
   EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', @iRtn Out, @Attachments

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
   EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body

   EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
   IF @hr <> 0
   Begin

      Select @output = 'Error Sending e-mail.'

   End
   Else
        Print 'Mail Succesfully sent to '+ @To


-- 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 have to.
-- Clean up the objects created.
   EXEC @hr = sp_OADestroy @iMsg
   

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

====================================================================
把你的SMTP 填上!!不然送不出!!!
把它COMPILE 在MS SQL SERVER 的MASTER, 然后
/*
exec sp_send_cdosysmail '','','','','','',''
*/

exec sp_send_cdosysmail '送信人的EMAIL','收信人的EMAIL','要CC的EMAIL,不然放空','BCC的EMAIL, 不然放空','标题','EMAIL内容','附件!FILE一定要放在SQL SERVER的HDD. 不然放空'


P/S:不明白可以问我. 还有一个是我MODIFIED 了用来送EMAIL TO SMS 的. 有谁要BROADCAST 可以找我.哈哈

坛主请加精......

[ 本帖最后由 lyfatt3 于 1-9-2005 03:51 PM 编辑 ]

lyfatt3

有谁拿了FREE SOURCE 请留下大名,蔼我花了很多时间TEST的,就当是鼓励鼓励吧!

虚拟.天

我不熟SQL Server，所以看了不是很懂。要问你也无从问起。不过我还是把CODE copy下来了，有空时就慢慢研究它，也许有一天会用得着。
谢谢你无私的分享