SQL Server SP_SEND_DBMAIL Image file attachment

Question

I am using a trigger on a table to send an email using sp_send_dbmail.

I want to include a file attachment in the email of an image type.

The raw data for the jpeg is stored in the ndl_Image column which is of type binary.

I have the following code:-

DECLARE @ReferenceID varchar(max)
DECLARE @Recipient varchar(Max)
DECLARE @Body varchar(max)
DECLARE @Subject varchar(max)
DECLARE @Q varchar(max)

--Get the EntryId and FormID for the inserted data.
SET @ReferenceID = 40
SET @Recipient = (SELECT ndl_CategorySendTo FROM ndl_config WHERE ndl_CategoryName = 'Dead Animal')
SET @Body = '<html>A new request has been created.</html>'
SET @Subject = 'NDL Report It: New Request #'+@ReferenceID
SET @Q = 'SELECT ndl_Image from dbo.ndl_data where ndl_ID ='+@ReferenceID
--Execute the stored procedure to send mail.
EXEC msdb.dbo.sp_send_dbmail

--Pass it the following paramaters.
@recipients=@Recipient,
@body=@Body, 
@subject=@Subject,
@profile_name='NDLProfile',
@body_format ='HTML',
    @execute_query_database='NDL_MX',
@query = @Q,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'image.jpg'

This works ok but seems to return the query as a text file if i comment out the last line.

How can I get the attachment as a jpeg file????

Thanks.

score 0 · Accepted Answer · answered Sep 13 '10 at 14:32

0

I don't think this is possible. As specified in the documentation for SP_SEND_DBMAIL:

"When a query is specified, the result set is formatted as inline text. Binary data in the result is sent in hexadecimal format."[emphasis added]

answered Sep 13 '10 at 14:32

Joe Stefanelli

132,803
19
237
235

I think you could accomplish this same idea however by first using BCP to export out to the file system. While not as elegant as attaching right from the varbinary the end goal could still be achieved. I posted some sample code as a concept here: http://stackoverflow.com/a/12432031/261997 – RThomas Sep 14 '12 at 21:48

score 0 · Answer 2 · answered Oct 19 '21 at 16:25

The sample below will imbed images into the < img > tag, which will work for jpeg, png, and such. It won't solve PDFs, but it does work with images, at least.

CREATE TABLE [dbo].[EmailAttachment](
  [EmailAttachmentID] [int] IDENTITY(1,1) NOT NULL,
  [MassEmailID] [int] NULL, -- foreign key
  [FileData] [varbinary](max) NOT NULL,
  [FileName] [varchar](100) NOT NULL,
  [MimeType] [varchar](100) NOT NULL )

-- ================================

 declare @eRecipient nvarchar(max) = 'me@example.com';
 declare @eSubject nvarchar(max) = 'Testing!';
 declare @FileName nvarchar(2000);
 declare @MimeType nvarchar(200);
 declare @attachText nvarchar(max);
 declare @eBody nvarchar(max) =  '<html><head><style>table, th, td {border-collapse: collapse;border: 1px solid black;} img {width: 100%; max-width: 640px;}</style></head>' + 
                             '<body><h1>Data with pics!</h1><table>' + 
                             '<tr><th>Some text</th><th>Some Pics</th></tr>';
 

  declare c1 cursor for
    select FileName,
        MimeType, 
          /* MimeType should be something like 'image/jpeg' or 'image/png' */
        cast('' as xml).value('xs:base64Binary(sql:column("FileData"))', 'varchar(max)') attachText
          /* the above uses XML commands to convert the binary attachment to UUencoded text */
    from EmailAttachment
    order by 1;

open c1;
fetch next from c1 into @FileName, @MimeType, @attachText
while @@FETCH_STATUS = 0
begin
    set @eBody = @eBody + '<tr><td>Filename: ' + @FileName + 
       '</td><td><img src="data:' + @contentType + ';base64,' +
        @AttachText + '="></td></tr>';
    /* note that the img tag contents the encoded image data, the mime type, and that it ends in an = sign. */
    fetch next from c1 into @FileName, @MimeType, @attachText
end;
close c1;
deallocate c1;
set @eBody = @eBody + '</table></body></html>';

exec msdb.dbo.sp_send_dbmail
    @recipients = @eRecipient, @body = @eBody, @subject = @eSubject, @body_format='HTML'

SQL Server SP_SEND_DBMAIL Image file attachment

2 Answers2

Linked