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))
@profile_name = 'Profile1',
@recipients = @RecipientEmail,
@subject = @Subject,
@body = @Body,
@importance = 'HIGH',
@query_attachment_filename = 'att.pdf',
@attach_query_result_as_file = 1,
@query = @query,
@query_no_truncate = 1,
@query_result_header = 0,
@exclude_query_output = 0,
@append_query_error = 1,
@query_result_width = 32767,
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.