locked
Sending contents of BLOB as attachment in sql mail RRS feed

  • Question

  • Hello,

    I store some zipfiles in sql table as blob. I would like to send this zipfile as an attachment with sql mail.

    Is this feasable?

    Please some advise on the way to do this.

     

    Thanks

    Sunday, May 1, 2011 11:37 AM

Answers

  • R-1966,

    instead of doing this inside SQL Server, try to do this in your .net Code  much simple and less error prone

    a) Read content of blob in your .net code

    b) send email attaching your blob (memorystream, you do not need to even save it on thd disk) using system.net.mail

     

    Example and sample Code 

    http://geekswithblogs.net/SanjayU/archive/2008/01/31/119155.aspx

    • Proposed as answer by Peja Tao Friday, May 27, 2011 3:11 PM
    • Marked as answer by Peja Tao Wednesday, June 8, 2011 2:17 AM
    Friday, May 27, 2011 3:08 PM

All replies

  • What version and edition of SQL Server are you using? SQL Mail is for SQL 2000 and if you are using SQL 2005 or later you need to use Database Mail. So answer depends on version and edition of sql server?

     



    Sunday, May 1, 2011 11:43 AM
  • I'm sorry not having mentioned the version of SQL.

    In fact, I'm using Database Mail on SQL 2005.

    Regards

    Monday, May 2, 2011 7:28 AM
  • in your case you have to send .zip file that is stored in SQL Server database table as  @query_attachment_filename =
    parameter of sp_send_dbmail stored proc. Change params to suit your needs.


    EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = 'ProfileNameHere',
     @recipients = 'email@test.com',
     @body = 'This is test mail. See attached file',
     @query = 'SELECT ZipColumn FROM DatabaseName
             WHERE <Criteria>',
     @query_attachment_filename = 'attachment.zip',
     @subject = 'Test Attachment',
     @attach_query_result_as_file = 1
    

    • Proposed as answer by Peja Tao Tuesday, May 3, 2011 6:07 AM
    • Marked as answer by r-1966 Wednesday, May 4, 2011 2:14 PM
    • Unmarked as answer by r-1966 Thursday, May 5, 2011 7:27 AM
    • Unproposed as answer by Peja Tao Friday, May 6, 2011 9:15 AM
    Monday, May 2, 2011 1:36 PM
  • In addition, please refer to this online document for more details.
    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, May 3, 2011 6:09 AM
  • Thanks a lot.

    Wednesday, May 4, 2011 2:15 PM
  • Hello,

    I'v tried this code, and yes, the file is being send as an attachment. Problem is that it does not open.

    We did some tests with zip-files and html-files with every time the same result. The attachment won't open.

    Any advise?

    Thanks

    Thursday, May 5, 2011 7:30 AM
  • Hi ,

    As you have stored some zipfiles in sql table as blob, could you please try to set @query_attachment_filename = NULL? So the attachment file name will be automatically named according to the result from blob column.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, May 5, 2011 8:55 AM
  • If I do that, i get a mail with an attachment with a .txt extension. The contents is garbled and is starts with the fieldname of where the blob is stored. The last row of the text is '(1 row affected)

    I noticed that when I provide the filename in the @query_attachment_filename, the filename is correct, but the size is a lot smaller than the original zipfile????

    ????

    Thursday, May 5, 2011 1:04 PM
  • Hi r-19966,
    The attachment send in the mail is just the title other than the content in your blob column. That is the reason why the size is a lot small than the original zip file. So I did not suggest to use @query and @query_attachment_filename here. 
    You could use a bcp command to export the zip file to a location from the table  like :
    EXEC master..xp_cmdshell 'bcp "SELECT zipcolumn FROM [Test].dbo.[tableName] where [criteira] " queryout E:\Test\attachment.zip  -c -T'
    Please refer to more details about bcp utility through this online document.
    And then execute sp_send_dbmail to attach the zip file and send a mail like :
    EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = 'ProfileNameHere',
     @recipients = 'email@test.com',
     @body = 'This is test mail. See attached file',
     @file_attachments=’ E:\Test\attachment.zip ‘
    @subject = 'Test Attachment',
    Hope this helps.

    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by r-1966 Friday, May 6, 2011 11:27 PM
    • Unmarked as answer by r-1966 Thursday, May 26, 2011 5:35 AM
    Friday, May 6, 2011 9:39 AM
  • Thanks Peja,

    this seems to be a correct sollution.

    The only thing I want to remark is that should use the -n parameter in stead of the -c parameter.

     EXEC master..xp_cmdshell 'bcp "SELECT zipcolumn FROM [Test].dbo.[tableName] where [criteira] " queryout E:\Test\attachment.zip  -n -T'
    Thanks a lot for the help

     

    Friday, May 6, 2011 11:27 PM
  • Hello,

    stil got a problem with bcp of blobfield with a file inside.

    Every time I do a bcp to a file on the filesystem, some strange characters are put in front of the original data (ŽÆ [NULL][NULL]).

    For a zipfile for instance, this has the consequence that the file can be opened with winzip, but not with the compressed folder feature in windows.

    Anyone some advice???

    Thanks

    • Marked as answer by r-1966 Thursday, July 7, 2011 6:01 PM
    • Unmarked as answer by r-1966 Thursday, July 7, 2011 6:02 PM
    Wednesday, May 25, 2011 5:58 PM
  • R-1966,

    instead of doing this inside SQL Server, try to do this in your .net Code  much simple and less error prone

    a) Read content of blob in your .net code

    b) send email attaching your blob (memorystream, you do not need to even save it on thd disk) using system.net.mail

     

    Example and sample Code 

    http://geekswithblogs.net/SanjayU/archive/2008/01/31/119155.aspx

    • Proposed as answer by Peja Tao Friday, May 27, 2011 3:11 PM
    • Marked as answer by Peja Tao Wednesday, June 8, 2011 2:17 AM
    Friday, May 27, 2011 3:08 PM