How to save an image from SQL Server to a file using SQL

Question

Question

In SQL Server 2005, I have a table with images (data type: image). Using only SQL, how do I save an image as a file (on the same server that SQL Server is running). If I have to, I'll use SQL CLR, but I want to avoid that if possible.

Background

I want a SQL Server job to run no a schedule that calls a proc that will send emails with embedded images using SQL Server Database Mail like this:

exec msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = '[email protected]',
@subject = 'hello',
@file_attachments = 'C:\MyLogo.gif',
@body=N'<p>Image Test</p><img src="MyLogo.gif" /><p>See image there?</p>', 
@body_format = 'HTML';

That SQL works, but I need to get the image saved as a file first. If I can get the image directly on the email without saving it as a file, that's fine, but it needs to be embedded on the email and I only want to use SQL.

Solution 1

I have tried using the @attach_query_result_as_file option but there is no way to have that query written as binary that I can get to work. So the next option would be to use bcp to a temp file and then attach the file.

DECLARE @sql VARCHAR(1000)

SET @sql = 'BCP "SELECT ImageColumn FROM YourTable where id = 1 " QUERYOUT C:\TEMP\MyLogo.gif -T -fC:\TEMP\bcpFormat.fmt -S ' + @@SERVERNAME
EXEC master.dbo.xp_CmdShell @sql

exec msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = '[email protected]',
@subject = 'test as image',
@body=N'<p>Image Test</p><img src="MyLogo.gif" /><p>See image there?</p>', 
@file_attachments = 'C:\TEMP\MyLogo.gif',
@body_format = 'HTML';

The bcpFormat.fmt would look like this:

8.0
1
1 SQLIMAGE 0 0 "" 1 Image ""

That will attach the image from your database to the email as a file. It still won't show it "inline" as that would take some more work to mime encode the image into the body of the email and reference it from your html. You would also need to generate some unique names for your files and cleanup so that multiple processes won't step on each other.

Solution 2

This article explains how you can use SQL Server's OLE automation stored procs (which allow you to create and use COM-based objects through T-SQL) to create a file.