Saturday, August 9, 2008

Creating Files from the images stored in Binary format in the database table.

Problem :-
Images are stored in a database table-Varbinary (SQL Server 2005) column. Form the stored images we need to create individual image files.

Solution :
BCP QUERYOUT with little format file tweaking can be a wonderful solution for this. You can include this BCP queryout in a XP_CMDShell dynamic sql from SQL Env and it will provide a simple solution to this complex requirement.

Demo environment details
Database : AdventureWorks
Table : ProductionPhoto
Image Column : LargePhoto
There are 101 rows in this table, each one having a LargePhoto column with some image. We need to create 101 JPG files from this table. Here we go…

Step 1 : Creation of Format File

C:\>bcp AdventureWorks.Production.ProductPhoto format nul -T -n -f c:\PP.fmt

Step 2 : Format file will looks something like this

Step 3 : Tweak Format file : We need to get the LargePhoto column binary data.

• Remove all column except LargePhoto
• Change Prefix of LargePhoto column from 8 to 0 (zero)
• Change Number of column from 6 to 1
• Change column sequence from 4 to 1
• After the ncessary modification the format file should look like next sceen

Step 4 : Use BCP QUERYOUT to create Image files

My BCP QUERYOUT query is this
C:\>bcp "SELECT top 1 LargePhoto FROM AdventureWorks.Production.ProductPhoto wh
ere ProductPhotoID=69" queryout c:\ProductPhotoID_69.jpg -T -fC:\PP.fmt

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (1000.00 rows per sec.)


The above BCP Queryout statement is created a file ProductPhotoID_69.jpg . Now let us go and open that form windows

Double click the file and the file opened

Step 5 : Loop through all the records in this table and create respective files of each row in C:\Photo folder


DECLARE @ProductPhotoID int, @Sqlstmt varchar(4000),@LargePhotoFileName varchar(200)

DECLARE Cursor_ProductPhoto CURSOR FOR
SELECT ProductPhotoID, LargePhotoFileName
from AdventureWorks.Production.ProductPhoto
ORDER BY ProductPhotoID

OPEN Cursor_ProductPhoto

FETCH NEXT FROM Cursor_ProductPhoto
INTO @ProductPhotoID, @LargePhotoFileName

PRINT @LargePhotoFileName

-- Framing DynamicSQL for XP_CMDshell

Set @Sqlstmt='bcp "SELECT top 1 LargePhoto FROM AdventureWorks.Production.ProductPhoto where ProductPhotoID='+str(@ProductPhotoID)+'" queryout c:\Photo\'+ltrim(@LargePhotoFileName)+' -T -fC:\PP.fmt'
print @sqlstmt
exec xp_cmdshell @sqlstmt
FETCH NEXT FROM Cursor_ProductPhoto
INTO @ProductPhotoID, @LargePhotoFileName
CLOSE Cursor_ProductPhoto
DEALLOCATE Cursor_ProductPhoto


Check this Screen. The above script has created individual files in this folder

Summary :-

This solution provides very simple method to create files from the binary stored in Database by little tweaking in Format file. If you want to keep a backup of Images in file system format then this method can really help.


BauwensER said...

First of all I would like to thanks you for replying to my question in the T-SQL forum. I'm trying to reconstruct all our images that are stored in a particular table but I'm having a problem when I execute the following statement:

EXEC xp_cmdshell 'bcp "MYDATABASE.PEOPLE.PICTURE" queryout format nul -T -n -f C:\pp.fmt'

The following error is produced:

Unknown argument 'nul' on command line.

Madhu K Nair said...

Remove QueryOut ... Try the following statement

EXEC xp_cmdshell 'bcp "MYDATABASE.PEOPLE.PICTURE" format nul -T -n -f C:\pp.fmt'


Doug Purnell said...

Thanks for this explaination of exporting image files. I was able to take your process and tweek it for our table structure and produce the necessary results.

Thanks Again


Locations of visitors to this page