How to get an image out of a table in SQL Server

Let's suppose you have a Binary File stored in an Image field in a Sql Server Table and you need it Exported. SQL Server Management Studio only allows access to a small subset of the data and even then it is encoded. You could write a .NET program to extract the image but that is time consuming. An easier way to extract the image or XML is to use the following steps. First enable the functionality that will extract the data.

sp_configure 'show advanced options', 1;
go
reconfigure;
go
sp_configure 'Ole Automation Procedures', 1;
go
reconfigure;
go

Then use the following to actually extract the image from the row. The above is optional, the next steps are required. Optional in that its only needed if its not already set. The file will be written to the database server file system, not the user who requested the file.

Declare @VarOut varbinary(max)

Select @VarOut = ImageFile from Images where ID = 4

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, @VarOut;
exec sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'c:\temp\image.png', 2;
exec sp_OAMethod @ObjectToken, 'Close';
exec sp_OADestroy @ObjectToken;

Tags - SQL

Last Modified : June 2023