Dagim Belayneh Dagim Belayneh - 6 months ago 93
SQL Question

SQL sp_send_dbmail send email with attachment, pdf file got corrupted

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.Publications where PublicationID = '+ CAST(@PublicationID 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.

Answer

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.