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.)
C:\>
=====================================================================================
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
--==============================================================================
SET NOCOUNT ON
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
WHILE @@FETCH_STATUS = 0
BEGIN
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
END
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.
Posts
