SQL sp_send_dbmail 发送带有附件的电子邮件，pdf 文件已损坏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37502480/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL sp_send_dbmail send email with attachment, pdf file got corrupted
提问by Dagim Belayneh
I have a stored procedure to send email, which will be invoke when a record is inserted to a table. I wanted to attach a document added to the table and send an email.I can get the attached file but its corrupted when I open it(It says file has been damaged).
我有一个发送电子邮件的存储过程，当将记录插入到表中时将调用该存储过程。我想附加一个添加到表格中的文档并发送一封电子邮件。我可以得到附加的文件，但是当我打开它时它已损坏（它说文件已损坏）。
Can anyone please help me? here is the code,I've excluded declare statements for simplicity.
谁能帮帮我吗？这是代码，为了简单起见，我排除了声明语句。
Select @query = 'set nocount on; select cast(Document as varchar(max)) from dbo.myTable where ID = '+ CAST(@ID as varchar(100))
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile1',
@recipients = @RecipientEmail,
@subject = @Subject,
@body = @Body,
@importance = 'HIGH',
@query_attachment_filename = 'att.pdf',
@attach_query_result_as_file = 1,
@query_result_no_padding=1,
@query = @query,
@query_no_truncate = 1,
@query_result_header = 0,
@exclude_query_output = 0,
@append_query_error = 1,
@query_result_width = 32767,
@body_format ='HTML'
I appreciate any help. Thanks in advance.
我很感激任何帮助。提前致谢。
采纳答案by Dagim Belayneh
The only way I could accomplish this was by temporarily exporting the file to local folder and then attach it to the email.
我可以完成此操作的唯一方法是将文件临时导出到本地文件夹，然后将其附加到电子邮件中。
set @Pid=CAST(@ID as varchar(100))
SELECT @query= 'BCP "SELECT Document from myDB.dbo.myTable where ID ='+@Pid+'" queryout "E:\Log_Files\Attached.pdf" -T -N'
EXEC xp_cmdshell @query; --, NO_OUTPUT;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile1',
@recipients = @InviteeEmail,
@subject = @ReferenceNo,
@body = @PublicationContent,
@importance = 'HIGH',
@file_attachments = 'E:\Log_Files\Attached.pdf',
@body_format ='HTML' --because
END
Go
You can read more about the BCP utility here.
您可以在此处阅读有关 BCP 实用程序的更多信息。
回答by Balaji Dinakaran
Using SQL server 2014. Give table name in 3 part. servername.dbname.dbo.tblname
使用 SQL Server 2014。在 3 部分给出表名。服务器名.dbname.dbo.tblname
DECLARE @Body VARCHAR(8000)
DECLARE @Qry varchar(8000)
SET @Body ='Hi All,'
SET @Qry='set nocount on;select * from tbl_name'
DECLARE
@tab char(1) = CHAR(9)
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'mailid',
@blind_copy_recipients='mailid',
@body= @Body,
@query=@Qry,
@subject='Send db mailer with attachment',
@attach_query_result_as_file = 1,
@query_attachment_filename='filename.xls',
@query_result_separator=@tab,
@query_result_no_padding=1
