The solution can not attach binary object stored in db field, you may change your schema a little bit store the path to the binary file. if you could enable .net clr in your database server, you will have more options.
use master
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_send_cdosysmail]')
and objectproperty(id, N'isprocedure') = 1)
drop procedure [dbo].[usp_send_cdosysmail]
go
create procedure usp_send_cdosysmail
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body nvarchar(max) ,
@smtpserver varchar(25),
@bodytype varchar(10) ,
@attachment varchar(100)= ' '
as
declare @imsg int
declare @hr int
declare @source varchar(255)
declare @description varchar(500)
declare @output varchar(1000)
exec @hr = sp_oacreate 'cdo.message', @imsg out
exec @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'
exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', @smtpserver
exec @hr = sp_oamethod @imsg, 'configuration.fields.update', null
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, @bodytype, @body
-- Attachments...
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
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
exec @hr = sp_oadestroy @imsg
go
set quoted_identifier off
go
set ansi_nulls on
go
sp_configure 'Ole Automation Procedures', 1
RECONFIGURE
GO