SQL Server SP_SEND_DBMAIL 图像文件附件

发布于 2024-09-18 21:37:49 字数 1111 浏览 7 评论 0原文

我正在表上使用触发器来使用 sp_send_dbmail 发送电子邮件。

我想在图像类型的电子邮件中包含文件附件。

jpeg 的原始数据存储在二进制类型的 ndl_Image 列中。

我有以下代码：-

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'

这工作正常，但如果我注释掉最后一行，似乎会将查询作为文本文件返回。

如何获取 jpeg 文件形式的附件？？？

谢谢。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

滥情哥ㄟ 2024-09-25 21:37:49

我认为这是不可能的。正如 SP_SEND_DBMAIL 文档中所述：

“当指定查询时，结果
set 的格式为内联文本。
结果中的二进制数据被发送进来
十六进制格式。“[添加强调]

回复收藏 0 原文

倾｀听者〃 2024-09-25 21:37:49

下面的示例将图像嵌入到 <图像>标签，适用于 jpeg、png 等。它不能解析 PDF，但至少可以处理图像。

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) = '[email protected]';
 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'

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) = '[email protected]';
 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'

回复收藏 0 原文

~没有更多了~