2

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.

Dagim Belayneh
  • 141
  • 1
  • 2
  • 6

3 Answers3

3

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.

Dagim Belayneh
  • 141
  • 1
  • 2
  • 6
1

Using SQL server 2014. Give table name in 3 part. servername.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
Balaji Dinakaran
  • 345
  • 2
  • 8
0

Only textfiles seem to be supported as attachments, because of the way they are encoded. For a solution and some more info you can look at this previously asked question.

Community
  • 1
  • 1
Fredrik Lundvall
  • 116
  • 2
  • 7