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.

Answer 1

I think madC's comment is the best solution. You can also try using something like this :

INSERT INTO OPENROWSET ( 'BULK', '', BULK_BLOB ) ...

but on my side it required the following additional steps:

sp_configure 'show advanced options', 1
reconfigure with override
sp_configure 'Ad Hoc Distributed Queries', 1 
reconfigure with override

And after this the result of the INSERT was:

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "BULK" for linked server "(null)".

Maybe you will be able to find the idea how to make it work.

Answer 2

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.

Answer 3

Is an SQL CLR (.NET) stored procedure a possiblity? That would have no problem saving data from a blob field to a temporary file on disk.

Answer 4

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.

Answer 5

Sending email from sql server is limited and unreliable you would be better off with a scheduled console app IMO.