0

This is my query and I manage to make it work to send emails.

I have 2 columns, FILENAME is the name of file and ATTACHMENTFILE is the physical file stored as VARBINARY(MAX).

I want to query out the attachment file and send out as email. So far what I am able to do is query out the file name and varbinary which is some hexadecimal value in a text file. And NOT the actual content of the file itself

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'Notifications',
     @recipients = 'myemail@gmail.com',
   @execute_query_database = 'SERVER_DB',
   @query = 'SELECT FILENAME, ATTACHMENTFILE FROM SERVER_DB.dbo.EMAIL_QUEUE WHERE TRXID =2',
   --@query_attachment_filename = 'attachment.txt',
   @attach_query_result_as_file = 1,
   --@exclude_query_output = 1,
   --@query_result_header = 1,
   --@append_query_error = 1,
     @body = 'The database mail configuration was completed successfully.',
   @body_format = 'TEXT',
     @subject = 'Automated Success Message'
   ;
GO
RN92
  • 1,380
  • 1
  • 13
  • 32
KIRPAL SINGH
  • 185
  • 2
  • 17
  • I tried to follow this but failed too. https://stackoverflow.com/questions/35683593/send-image-stored-in-image-field-in-body-of-email-sp-dbsend-mail – KIRPAL SINGH Mar 05 '19 at 03:59
  • Try in ssis or ssrs – Chanukya Mar 05 '19 at 04:19
  • @Chanukya I am using SQL Express – KIRPAL SINGH Mar 05 '19 at 04:21
  • This explains it in some detail: https://dba.stackexchange.com/questions/168395/execute-sql-statement-and-send-result-to-email – Alex Mar 05 '19 at 04:25
  • Use the `file_attachments` parameter. I'm going to mark as a dupe against the linked answer as it has the solution – Nick.Mc Mar 05 '19 at 04:32
  • Possible duplicate of [Send image stored in image field in body of Email sp\_dbsend\_mail](https://stackoverflow.com/questions/35683593/send-image-stored-in-image-field-in-body-of-email-sp-dbsend-mail) – Nick.Mc Mar 05 '19 at 04:32
  • @Nick.McDermaid Yes, u just said what i used as reference (link in comment). But my file is empty. It does get generated at specified path but the contents are empty – KIRPAL SINGH Mar 05 '19 at 04:47
  • Oh sorry didn't realise that was your comment. So the file is created but it's empty? Is the file actually 0Kb? If you open it in notepad do you see anything? Are you checking the exported file in the filesystem directly? Looks like the export code was taken from here: https://stackoverflow.com/questions/4056050/script-to-save-varbinary-data-to-disk. That implies that the code works. All I can suggest is that you try very basic T-SQL code to export the file and put some `PRINT` statements in to debug, i.e. `PRINT CAST(DATALENGTH(@VarbinaryVariable) AS VARCHAR(10))` – Nick.Mc Mar 05 '19 at 04:58
  • I don't really get, what you want to achieve... What is the content of `ATTACHMENTFILE`? Is it an image? You might use `FOR XML PATH('')` and embedd the binary as `base64` encoded string. You can create `XHTML` where an image is directly embedded and can be opened in a browser or can be appended to a HTML-email. – Shnugo Mar 05 '19 at 07:44

0 Answers0