How to save image from SQL Server to file using SQL

Question

Question

In SQL Server 2005, I have a table with images (data type: image). How to use only SQL, how to save the image as a file (on the same server as SQL Server). If I need, I will use the SQL CLR, but I want to avoid this if possible.

Background

I want the SQL Server job to not execute the schedule that proc calls, which will send emails with embedded images using SQL Server Database Mail as follows:

exec msdb.dbo.sp_send_dbmail @profile_name = 'MyProfile', @recipients = '[e-mail]', @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'; 

This SQL works, but I need to save the image as a file first. If I can get the image directly to an email inbox without saving it as a file, that’s fine, but I need to embed it in email, and I want to use only SQL.

Answer 1

I tried using the @attach_query_result_as_file parameter, but there is no way for this query to be written as a binary file that I can get to work. So the next option would be to use bcp for the temporary 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 = '[e-mail]', @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'; 

bcpFormat.fmt will look like this:

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

This will attach the image from your database to the email as a file. It still will not show it "inline", as this will require additional work to mime encode the image in the body of the message and refer to it from your html. You will also need to generate some unique names for your files and cleanup so that several processes do not step on each other.

Answer 2

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

Answer 3

Is SQL CLR (.NET) stored procedure accessible? This will not prevent you from saving data from the blob field to a temporary file on disk.

Answer 4

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

Paste in postcard ('BULK', '', BULK_BLOB) ...

but on my side it took the following extra steps:

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

And after that, the result of INSERT was:

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

You may be able to find an idea how to make it work.