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

У меня есть вопрос из двух частей, касающийся отправки результатов запроса в виде вложений с использованием 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. Для краткости я исключил операторы declare.

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 = '[email protected]',
    @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. Но явно оригинальные заголовки потеряны. Также стоит отметить, что размеры вложенных файлов отличаются от исходных файлов. Это, скорее всего, также связано с неправильной кодировкой. Итак, я надеюсь, что есть способ создать заголовки файлов, используя сохраненный MIME-тип, или какой-то способ включить заголовки файлов в двоичные данные?

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


sql sp-send-dbmail
person Mindstorm Interactive    schedule 14.09.2012    source источник

Ответы (1)


arrow_upward
8
arrow_downward

Я не думаю, что вы сможете отправлять двоичные данные напрямую из 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 = '[email protected]',
        @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
person RThomas    schedule 14.09.2012
comment
Спасибо за ваш ответ. Похоже, это могло бы работать, но ограничения безопасности запрещают запуск xp_cmdshell, особенно с файлами, загруженными пользователем. Похоже, мне придется использовать другой подход. - person Mindstorm Interactive; 18.09.2012
comment
удачи в этом вопросе. - person MUHAMMAD MUBUSHER ASLAM; 22.11.2016