SQL sp_send_dbmail send email with attachment, pdf file got corrupted

Question

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.

Answer 1

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.