How to attach a file to an email using SQL stored procedure?

Question

Can't seem to find much information about how to attach a file that's been stored as a BLOB as part of an email.

I know you can attach files from the file system (C:\temp...) to email that are being setup in DBMAIL or custom stored procedures. However, I haven't seen any references to attaching something such as a PDF that's been stored as a binary object in a table.

I see where you can attach a query as a file, but I don't think that's what I'm looking for.

We would do this programatically via the application, but we need to be able to kick this SP via triggers or the application's server side code.

Is this possible or should i be asking other questions, since usually a binary object also needs to have a content type associated with it for browsers or mail objects to know how to handle?

Answer 1

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