How to Export Image Field to File

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

Export images from a SQL Server using Stored procedures

try the following query:

EXEC sp_configure 'show advanced options', 1 

GO 
RECONFIGURE 
GO 
EXEC sp_configure 'xp_cmdshell', 1 
GO 
RECONFIGURE 
GO

EXEC master..xp_cmdshell 'mkdir C:\ImagesFromSql'


DECLARE @ID as int
DECLARE @SQL as varchar(4000)

DECLARE  csr  CURSOR FOR SELECT ID FROM [FootballTeam].[dbo].[Players]

OPEN csr

FETCH NEXT FROM csr INTO @ID

WHILE @@FETCH_STATUS = 0 
BEGIN


SET @SQL = 'BCP "SELECT Photo FROM [FootballTeam].[dbo].[Players] WHERE ID = "' + CAST(@ID as varchar(10)) + ' queryout "C:\ImagesFromSql\' + CAST(@ID as varchar(10)) + '.jpg" -T -N'

EXEC master..xp_cmdshell  @SQL

FETCH NEXT FROM csr INTO @ID

END


CLOSE csr
DEALLOCATE csr

Export Images stored in DB as hex files in folder

You can use BCP:

The bulk copy program utility (bcp) bulk copies data between an
instance of MicrosoftSQL Server and a data file in a user-specified
format. The bcp utility can be used to import large numbers of new
rows into SQL Server tables or to export data out of tables into data
files.

here you have definition: LINK1

and here you have an example: LINK2

hope this help!

Saving image in database field to file on server

Based on the comment from Patrick and the answer in fastest way to export blobs from table into individual files I was able to store the images on my fileserver.

we ended up writing a CLR function something like the one given.

---

Related Topics