multiple attachments

  • I got this script from my DBA and it works fine for single attachments. I need help in modifying it to include multiple attachments

    ------------------------------------------------------------------------------------------

    -- Stored Procedure "dbo. send_smtp_mail"

    ------------------------------------------------------------------------------------------

    if exists( select * from dbo.sysobjects where id = object_id( '[dbo].[ send_smtp_mail]'))

    drop procedure [dbo].[send_smtp_mail]

    go

    create procedure [dbo].[send_smtp_mail]

    @subject varchar(255),

    @body varchar(4000),

    @from varchar(255),

    @to varchar(255),

    @cc varchar(255),

    @bcc varchar(255),

    @server varchar(255),

    @attachment varchar(255) = null

    as begin

    set nocount on

    set transaction isolation level read uncommitted

    declare @message_id int

    declare @return_code int

    declare @source varchar(255)

    declare @description varchar(512)

    -- Open a connection to the SMTP server.

    exec @return_code = sp_OACreate 'CDO.Message', @message_id out

    exec @return_code = sp_OASetProperty @message_id, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value', '2'

    exec @return_code = sp_OASetProperty @message_id, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @server

    exec @return_code = sp_OAMethod @message_id, 'Configuration.Fields.Update', null

    -- Define the message.

    exec @return_code = sp_OASetProperty @message_id, 'To', @to

    exec @return_code = sp_OASetProperty @message_id, 'From', @from

    exec @return_code = sp_OASetProperty @message_id, 'Subject', @subject

    exec @return_code = sp_OASetProperty @message_id, 'CC', @cc

    exec @return_code = sp_OASetProperty @message_id, 'BCC', @bcc

    exec @return_code = sp_OASetProperty @message_id, 'ReplyTo', @to

    exec @return_code = sp_OASetProperty @message_id, 'TextBody', @body

    -- Add attachment if it exists.

    if @attachment is not null

    begin

    exec @return_code = sp_OAMethod @message_id, 'AddAttachment', null, @attachment, ''

    -- print @attachment

    end

    -- Send the mail.

    exec @return_code = sp_OAMethod @message_id, 'Send', null

    -- Did we take an error?

    if @return_code != 0

    begin

    -- Yes. Display error code.

    print 'Error ' + convert(varchar, @return_code)

    -- Get the error message.

    exec @return_code = sp_OAGetErrorInfo null, @source out, @description out

    if @return_code = 0

    begin

    -- Display the error message.

    print '[' + isnull(@source, '') + ']' + isnull(@description, '')

    end

    else

    begin

    print 'sp_OAGetErrorInfo failed.'

    return

    end

    end

    exec @return_code = sp_OADestroy @message_id

    end

    go

  • Nevermind got it to work. Found the following script for multiple attachments. All I needed was to create a table-valued function called fn_split() based on this link http://blogs.interfacett.com/fn_split-table-valued-function-by-sql-instructor-jeff-jones

    IF @attachment IS NOT NULL AND LEN(@attachment) > 0 BEGIN

    Declare @files table(fileid int identity(1,1),[file] varchar(255))

    Declare @file varchar(255)

    Declare @filecount int ; set @filecount=0

    Declare @counter int ; set @counter = 1

    DECLARE @outVar INT

    SET @outVar = NULL

    INSERT @files SELECT cValue FROM master..fn_split(@attachment,',')

    SELECT @filecount=@@ROWCOUNT

    WHILE @counter<(@filecount+1)

    BEGIN

    SELECT @file = [file]

    FROM @files

    WHERE fileid=@counter

    EXEC @hr = sp_OAMethod @imsg, 'AddAttachment',@outVar OUT, @file

    SET @counter=@counter+1

    END

    END

  • Is there a reason for using sp_OA methods instead of sp_send_dbmail?

    sp_OA utilities memory from outside of the allocated server RAM, so if you have a heavily memory constrained system, sp_OA calls might not be the best to use

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply