October 14, 2015 at 9:37 am
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
October 15, 2015 at 9:49 am
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
October 16, 2015 at 2:14 am
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