sp_send_dbmail прикрепить файлы, хранящиеся в базе данных как varbinary

Question

У меня есть вопрос из двух частей, относящийся к отправке результатов запроса в виде вложений с использованием sp_send_dbmail.

Проблема 1: Открываются только основные файлы.txt. Любой другой формат, например.pdf или.jpg, поврежден.

Проблема 2: При попытке отправить несколько вложений, я получаю один файл со всеми склеенными именами файлов.

Я использую SQL Server 2005, и у меня есть таблица, хранящая загруженные документы:

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 Send Mail. Для краткости я исключил заявленные заявления.

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 = '[e-mail]',
    @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, или какой-нибудь способ включить заголовки файлов в двоичные данные?

Я также не уверен в значениях последних нескольких параметров, и я знаю, что объединение не совсем правильно, потому что оно добавляет к первому имени файла запятую. Но хорошую документацию почти невозможно найти. Пожалуйста помоги!

Answer 1

Я не думаю, что вы сможете отправлять двоичные данные напрямую из SQL. Есть несколько постов, в которых говорится об этой же проблеме. Из документации Microsoft текстовый возврат к запросу форматируется как текстовый файл. Двоичный формат отформатирован как шестнадцатеричный. Который, как вы указали, повреждает любой файл, который не является текстовым документом.

Я думаю, что вы все еще можете выполнить то, что вы пытаетесь сделать, сначала используя BCP для экспорта двоичных данных в файловую систему, а затем импортируя их обратно с помощью традиционных методов прикрепления файлов, доступных для sendmail.

Ну как то так. (только концепция - непроверенный код)

DECLARE @OutputFileAndPath VarChar(500) = '\\Log_Files\MyFile.pdf ' 
DECLARE @sql VarChar(8000)

SELECT @sql = 'BCP "SELECT MyFile FROM [dbo].[MyTable] 
    WHERE PrimaryKey = 12345" queryout ' + @OutputFileAndPath +
        ' -S MyServer\MyInstance -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 = '[e-mail]',
        @subject = @Subject,
        @body =@Body,
        @body_format ='HTML',
        @file_attachments = @OutputFileAndPath /* i.e. \\Log_Files\MyFile.pdf */

    update MassEmailset status= 30,SendDate = GetDate() where MassEmailID = @MassEmailID
end     

Answer 2

В приведенном ниже примере изображения будут вставлены в тег <img>, который будет работать с jpeg, png и т. Д. Он не решает PDF-файлы, но, по крайней мере, работает с изображениями.

       declare @eRecipient nvarchar(max) = '[e-mail]';
 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'