How to Save an Image from SQL Server to a File Using SQL

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

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 = 'bob@hotmail.com',
@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.

How to get Image data from SQL Server, Write To File, Save File to Disk using C#

Thanks for the input everyone. After doing some additional testing, I think that the data being retrieved from the database has been compressed in some way. I'm able to insert and pull out files programmatically just fine, the problem comes in when the application puts the data into the database (3rd party software). I'm going to look into the compression what I can come up with.

How to retrive image from a table and save it into a folder in different server using sql query

You can't perform a File System Operation using SQL Server alone. So If you want to save an Image Stored in SQL Server database as a File in your File System, Try using C# or any such Front End Languages to retrieve the Record from the Database and then Save to the Desired Location

How to save a combination of text and image in SQL Server database

You can convert an image to text with Base64, for example:

var image = File.ReadAllBytes("image.jpg");

var base64String = Convert.ToBase64String(image);

And you can convert it back to an image again:

var image = Convert.FromBase64String(base64String);

File.WriteAllBytes("image.jpg", image);

You just need to figure out how to split/concatenate your text as a whole...

How to export image field to file?

-- Write all database images (jpg) to file.
--------- --------- --------- --------- --------- --------- --------- 
DECLARE CURSOR_ProductIds CURSOR FOR (SELECT ImgImagesId FROM ImgProductSample)

DECLARE @ProductId INT;

OPEN CURSOR_ProductIds

FETCH NEXT FROM CURSOR_ProductIds INTO @ProductId
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  DECLARE @ImageData varbinary(max);
  SELECT @ImageData = (SELECT convert(varbinary(max), ImageData, 1) FROM ProductImages WHERE Id = @ProductId);

  DECLARE @Path nvarchar(1024);
  SELECT @Path = 'C:\MyImages\Output';

  DECLARE @Filename NVARCHAR(1024);
  SELECT @Filename = (SELECT ImageFilename FROM ProductImages WHERE id = @ProductId);

  DECLARE @FullPathToOutputFile NVARCHAR(2048);
  SELECT @FullPathToOutputFile = @Path + '\' + @Filename;

  DECLARE @ObjectToken INT
  EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;
  EXEC sp_OASetProperty @ObjectToken, 'Type', 1;
  EXEC sp_OAMethod @ObjectToken, 'Open';
  EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData;
  EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2;
  EXEC sp_OAMethod @ObjectToken, 'Close';
  EXEC sp_OADestroy @ObjectToken;

  FETCH NEXT FROM CURSOR_ProductIds INTO @ProductId
END
CLOSE CURSOR_ProductIds
DEALLOCATE CURSOR_ProductIds

-- Make sure the following statement is executed to enable file IO
-- From http://msdn.microsoft.com/en-us/library/ms191188.aspx
--------- --------- --------- --------- --------- --------- --------- 
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

How Do You Save An Image Out of a SQL Database Into the File System Using only SQL?

With SQL 2k8 there is the new FILESTREAM type that covers such cases. Filestreams can be opened via the Win32 file access handle like any other file, but hey are integrated into the database from transaction and backup/restore point of view.

I had a similar issue in SQL 2k5 and my solution was to use a CLR stored procedure with EXTERNAL_ACCESS that was writing into the file system using C# file operations.

---

Related Topics