locked
Saving and Retrieving Images In SQL Server 2008 From T-SQL RRS feed

  • Question

  • I am currently working on a project where after the certian events a email gets sent out to the customer using sp_send_dbmail procedure.  The client wants to include the image of their logo on the email.  I figured out that in order to include the logo on the email I have to actually include the image file as an attachment.  I was able to add the file as an attachment by adding the physical path as the file attachment (For Example: C:\logo.jpg).  It ran fine in the stored procedure in the New Query window.  However, when I call the procedure from the application it fails because the SQL Server Login account that the application is using to authenticate cannot access the file system to get the file.  I would like to avoid security issues by adding the user the ability to access the file system, so I thought I would use SQL Server to store the image directly in the database and retrieve it from there.

    This way I can get the image into a variable and attach it to my email.

    For example:
        DECLARE @Logo  varbinary(max)

        SELECT @Logo = LogoImage
        From LogoFiles

        sp_send_dbmail @FileAttachment = @Logo,
                                   etc....

    How can I do this?  Is this the best approach?

    Any information would be helpful.

    Thank you in advance.
    Tuesday, January 12, 2010 1:08 AM

Answers

  • I found a way to include my image on the email.  I have a stored procedure called SendEmail.  This procedure based on the type of email I specify it is and the order number/request number it recieves.  It then dumps the parameters for the email into a table I created EmailQueue.  Then the last step of the SendEmail runs the job by executing the sp_startjob 'Job Name'.  Since the job runs as an sa it is able to access the file system.  The job sends the email.  Then if the email send was successful it deletes the row from the EmailQueue table.  If it is unsuccessful it retains the row and sends an email to the administrator stating the error of why it didn't send.

    If anyone needs any further information on how to accomplish this just let me know.

    Thanks.
    • Marked as answer by crusso0704 Thursday, January 14, 2010 12:40 AM
    Thursday, January 14, 2010 12:40 AM

All replies

  • I usually use a script task in SSIS to send emails. It's a very powerful tool. However, if you still need to use sp_send_dbmail, you need to send the email as an html. This link might be helpful


    Abdallah El-Chal, PMP, ITIL, MCTS
    Tuesday, January 12, 2010 2:13 AM
  • Abdshall,

    Thank you for the link.  I understand that I need to make the email html and use the <img> tag to show the image.  The real question is how do I store the actual image in SQL Server.  This way making it accessible through SQL instead of using the file store?

    Thanks.

    Tuesday, January 12, 2010 2:19 AM
  • Here is one way

    CREATE TABLE myTable(id int identity(1,1),
    		Document varbinary(max))
    
     INSERT INTO myTable(Document)
     SELECT * FROM OPENROWSET(BULK 'C:\Image1.jpg', SINGLE_BLOB) as ImageLoad


    Abdallah El-Chal, PMP, ITIL, MCTS
    Tuesday, January 12, 2010 2:25 AM
  • Thanks.

    So to get the actual image from SQL Server when I go to run my sp_send_dbmail and attach it as the file would it look like the following:

    DECLARE @Image varbinary(max)

    SELECT @Image = Document
    From MyTable

    Or do I need to include something to convert it from the binary back to the actual image?

    Tuesday, January 12, 2010 2:36 AM
  • I'm sure it has to be converted because if you query it you will see the binary code, but I'm not familiar with a way to do it in t-sql code. It has to be converted, saved, and then attached. 
    Abdallah El-Chal, PMP, ITIL, MCTS
    Tuesday, January 12, 2010 2:55 AM
  • I found a way to include my image on the email.  I have a stored procedure called SendEmail.  This procedure based on the type of email I specify it is and the order number/request number it recieves.  It then dumps the parameters for the email into a table I created EmailQueue.  Then the last step of the SendEmail runs the job by executing the sp_startjob 'Job Name'.  Since the job runs as an sa it is able to access the file system.  The job sends the email.  Then if the email send was successful it deletes the row from the EmailQueue table.  If it is unsuccessful it retains the row and sends an email to the administrator stating the error of why it didn't send.

    If anyone needs any further information on how to accomplish this just let me know.

    Thanks.
    • Marked as answer by crusso0704 Thursday, January 14, 2010 12:40 AM
    Thursday, January 14, 2010 12:40 AM
  • Could you post the solution? I know this thread is 2 years old.
    Wednesday, December 12, 2012 11:43 PM