sp_send_dbmail filestream column attachment

• james.friesen

  SSC Enthusiast

  Points: 186

  More actions

  February 2, 2019 at 10:26 am

  #370397

  Hi,
  I have a table with a varbinary(max) Filestream column.  I want to use the file as an email attachment with sp_send_dbmail 

  First, using Attachment.PhysicalPathName() I tried to attach the file using 

          EXEC msdb.dbo.sp_send_dbmail
              @profile_name = 'DefaultProfile',
              @recipients = 'user@user.com',
              @body = @EmailText,
              @subject = @EmailSubject,
              @file_attachments=@AttachmentPath

  the file attaches, but it uses the internal filestream name.  Copying the attachment from the email, and renaming it (in my case to test.pdf), I can open it okay.  Is there a way to rename the attachment file name.

  My other attempt was to create a query
     Declare @QueryText varchar(max) = 'SET NOCOUNT ON; SELECT Cast(Attachment as varbinary(max)) FROM ' + DB_NAME() + '.dbo.SendEmail Where SendEmailId = 1'

         EXEC msdb.dbo.sp_send_dbmail
              @profile_name = 'DefaultProfile',
               @recipients = 'user@user.com', 
              @body = @EmailText, 
              @subject = @EmailSubject,
      @query = @QueryText,
      @attach_query_result_as_file = 1,
      @query_attachment_filename=@AttachmentFileName,
      @query_no_truncate = 1,
      @query_result_header = 0,
      @query_result_width = 32767

  In this case, a file is attached with the proper name, but it won't open .  I get "the file has been damaged (for example, it was sent as an email attachment and wasn't correctly decoded).

  Is there a way I can get one of these two methods to work?

  Thanks in advance