Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Desiree Harris Sending SQL Notifications with CDOSYS RE: Sending SQL Notifications with CDOSYS

  • August 27, 2004 at 1:05 am

    #520811

    A friend of mine took the Microsoft example proc and produced a more robust version. It defaults to using the servername from @@servername if one is not provided, and it can send file attachments:

    use

    master

    go

    CREATE

    PROCEDURE [dbo].[sp_sendmail_CDOSYS]

    @To

    varchar(100) ,

    @Subject

    varchar(100)=" ",

    @Body

    varchar(4000) =" ",

    @Importance

    int = 1, -- 0=low, 1=normal, 2=high

    @cc

    varchar(100)= " " ,

    @Bcc

    varchar(100)= " " ,

    @Attachments

    varchar(8000)=NULL, /* seperated by ; */

    @HTMLFormat

    int = 0,

    @From

    varchar(100)= null ,

    @Server

    varchar(255) = null,

    @UserName

    varchar(255) = null,

    @password

    varchar(255) = null

    /*********************************************************************
    Created by : Corey Embry
    Description: Use CDOSYS to Send Mail using smtp
    Base Code was obtained from MS Article Q312839
     
    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/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
    exec sp_sendmail_CDOSYS
    @To = 'Coreye@avalamarketing.com'
    , @Subject = 'CDOSYStest' -- Optional
    , @Body ='this is a test' -- Optional
    , @Importance int = 1 -- Optional
    , @cc = 'mickey@disney.com' -- Optional
    , @Bcc = 'Minnie@disney.com' -- Optional
    , @Attachments = 'c:\test.txt' -- Optional ; Delimited
    , @HTMLFormat = 0 -- Optional Default Text
    -- , @From = 'Coreye@avalamarketing.com' -- Optional
    , @Server='avalafs1' -- Optional
    -- , @UserName= -- Optional
    -- , @Password= -- Optional
    ***********************************************************************/

    AS

    SELECT @Server = isnull(@Server, @@SERVERNAME)

    Select @From = isnull(@From, @@SERVERNAME)

    Declare @iMsg int

    Declare @hr int

    Declare @int int

    Declare @Tmp varchar(8000)

    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', @Server

    if @UserName is not null and @password is not null

    begin

    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/SendUserName").Value', @UserName

    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/SendPassword").Value', @Password

    end

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

    EXEC @hr = sp_OASetProperty @iMsg, 'CC', @cc

    EXEC @hr = sp_OASetProperty @iMsg, 'BCC', @BCC

    /* if there are any attachments */

    if

    len(@Attachments)>0

    begin

    while len(@Attachments)>0

    begin

    set @int = charindex(';',@Attachments)

    if @int > 0

    begin

    set @Tmp = left(@Attachments,@int-1)

    set @Attachments = right(@Attachments,(len(@Attachments)-@int))

    end else

    begin

    set @Tmp = @Attachments

    set @Attachments = ''

    end /* Add the Attachment */

    Print @Tmp

    EXEC @hr= sp_OAMethod @iMsg, 'AddAttachment', NULL, @Tmp

    end

    end

     
     

    -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.

    if

    @HTMLFormat <> 1

    begin

    EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body

    end
    else
    begin

    EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body

    end

    -- Save the configurations to the message object.

    EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

    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

    Kindest Regards,

    Clayton