sp_send_dbmail附加数据库中以varbinary存储的文件

我有一个由两部分组成的问题，涉及使用sp_send_dbmail将查询结果作为附件发送。

问题1:将只打开基本的.txt文件。任何其他格式如.pdf或.jpg都已损坏。

问题2:当尝试发送多个附件时，我收到一个文件，所有文件名都粘在一起。

我正在运行SQLServer2005，我有一个表存储上传的文档：

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

我还有一个MassEmail表格，里面有标准的电子邮件内容。这是SQL发送邮件脚本。为了简洁起见，我排除了声明语句。

while ( (select count(MassEmailID) from MassEmail where status = 20 )>0) 
begin
    select @MassEmailID = Min(MassEmailID) from MassEmail where status = 20
    select @Subject = [Subject] from MassEmail where MassEmailID = @MassEmailID
    select @Body = Body from MassEmail where MassEmailID = @MassEmailID
    set @query = 'set nocount on; select cast(FileData as varchar(max)) from Mydatabase.dbo.EmailAttachment where MassEmailID = '+ CAST(@MassEmailID as varchar(100))  
    select  @filename = ''
    select  @filename = COALESCE(@filename+ ',', '') +FileName from EmailAttachment where MassEmailID = @MassEmailID
exec msdb.dbo.sp_send_dbmail    
    @profile_name = 'MASS_EMAIL',
    @recipients = 'me@myemail.com',
    @subject = @Subject,
    @body =@Body,
    @body_format ='HTML',
    @query = @query,
    @query_attachment_filename = @filename,
    @attach_query_result_as_file = 1,
    @query_result_separator = '; ',
    @query_no_truncate = 1,
    @query_result_header = 0;
update MassEmailset status= 30,SendDate = GetDate() where MassEmailID = @MassEmailID
end

我能够成功地从数据库中读取文件，这样我就知道二进制数据没有损坏。

.txt文件仅在我将FilaData转换为varchar时读取。但很明显，原始标头已经丢失。同样值得注意的是，附件文件的大小与原始文件不同。这很可能也是由于编码不当造成的。所以我希望有一种方法可以使用存储的mimetype创建文件头，或者在二进制数据中包含文件头？

我也对最后几个参数的值没有信心，我知道coalize不太正确，因为它在第一个文件名前面加了一个逗号。但几乎不可能找到好的文档。请帮忙！

我认为您将无法直接从SQL发送二进制数据。有一些帖子也谈到了同样的问题。从Microsoft文档中，返回到查询的文本被格式化为文本文件。二进制被格式化为十六进制。正如你所指出的，它会破坏任何不是文本文档的文件。

然而，我认为您仍然可以通过首先使用BCP将二进制数据导出到文件系统，然后通过可用于发送邮件的传统文件附件方法将其导入，来完成您想要做的事情。

这样的事情。（仅概念-未经测试的代码）

DECLARE @OutputFileAndPath VarChar(500) = '\Log_FilesMyFile.pdf ' 
DECLARE @sql VarChar(8000)
SELECT @sql = 'BCP "SELECT MyFile FROM [dbo].[MyTable] 
    WHERE PrimaryKey = 12345" queryout ' + @OutputFileAndPath +
        ' -S MyServerMyInstance -T -fC:Documents.fmt'
/* you could use a generic format file that would cover most formats */
EXEC xp_cmdshell @sql, NO_OUTPUT;
while ( (select count(MassEmailID) from MassEmail where status = 20 )>0) 
begin
    select @MassEmailID = Min(MassEmailID) from MassEmail where status = 20
    select @Subject = [Subject] from MassEmail where MassEmailID = @MassEmailID
    select @Body = Body from MassEmail where MassEmailID = @MassEmailID

    exec msdb.dbo.sp_send_dbmail    
        @profile_name = 'MASS_EMAIL',
        @recipients = 'me@myemail.com',
        @subject = @Subject,
        @body =@Body,
        @body_format ='HTML',
        @file_attachments = @OutputFileAndPath /* i.e. \Log_FilesMyFile.pdf */
    update MassEmailset status= 30,SendDate = GetDate() where MassEmailID = @MassEmailID
end

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

 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'

相关内容

最新更新

热门标签：