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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy