SQL Server 2005/2008 : Export Blob or Image datatype from database.
Please refer to my previous blog post on how insert blob or images into SQL Server.
http://saveadba.blogspot.com/2011/12/sql-server-import-blob-or-images-using.html
There are a few VB scripts to extract or export blob/image data from SQL Server but I prefer the TSQL method. You need to enable the 'Ole Automation Procedures' sp_configure option. This option is disabled by default.
To enable Ole automation Procesdures use the following commands.
exec sp_configure 'show_advanced_options', 1
Reconfigure
go
exec sp_configure 'Ole Automation Procedures',1
Reconfigure
go
Use the following TSQL to extract all the blobs or images.
DECLARE @SQLIMG VARCHAR(MAX),
@IMG_PATH VARBINARY(MAX),
@TIMESTAMP VARCHAR(MAX),
@ObjectToken INT
DECLARE IMGPATH CURSOR FAST_FORWARD FOR
--The following line will select which record/image you want to extract.
--So change the following line to select your record.
SELECT document from Images where id=15
OPEN IMGPATH
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
--Set the path where you want to save the file and also the extension of the file.
--The file name will be the timestamp at which it was extracted.
SET @TIMESTAMP = 'C:\Pictures\Test\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.JGP'
PRINT @TIMESTAMP
PRINT @SQLIMG
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
END
CLOSE IMGPATH
DEALLOCATE IMGPATH
If you have saved the file name, extension and the original path in the table then use that in the @TIMESTAMP variable to set the path and file name where you want to save it.
Please refer to my previous blog post on how insert blob or images into SQL Server.
http://saveadba.blogspot.com/2011/12/sql-server-import-blob-or-images-using.html
There are a few VB scripts to extract or export blob/image data from SQL Server but I prefer the TSQL method. You need to enable the 'Ole Automation Procedures' sp_configure option. This option is disabled by default.
To enable Ole automation Procesdures use the following commands.
exec sp_configure 'show_advanced_options', 1
Reconfigure
go
exec sp_configure 'Ole Automation Procedures',1
Reconfigure
go
Use the following TSQL to extract all the blobs or images.
DECLARE @SQLIMG VARCHAR(MAX),
@IMG_PATH VARBINARY(MAX),
@TIMESTAMP VARCHAR(MAX),
@ObjectToken INT
DECLARE IMGPATH CURSOR FAST_FORWARD FOR
--The following line will select which record/image you want to extract.
--So change the following line to select your record.
SELECT document from Images where id=15
OPEN IMGPATH
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
--Set the path where you want to save the file and also the extension of the file.
--The file name will be the timestamp at which it was extracted.
SET @TIMESTAMP = 'C:\Pictures\Test\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.JGP'
PRINT @TIMESTAMP
PRINT @SQLIMG
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
END
CLOSE IMGPATH
DEALLOCATE IMGPATH
If you have saved the file name, extension and the original path in the table then use that in the @TIMESTAMP variable to set the path and file name where you want to save it.
Please provide the solution if we wish to save the file with one column name available in the table having the image data.
ReplyDeleteI didn't understand your requirement properly.
DeleteDo you want to import the images into a table or do you want to export the images?
I assume you are asking for a solution where there is only column in the table and you want to export all the files. Am I correct?
1) In the script there is line like
DeleteSELECT document from Images where id=15
Change this to SELECT from the table name that has the images. And SELECT The column name for the BLOB data.
Example, if your table name is imagetable and the column name is colname then use
SELECT colname from imagetable
2) In the script change the following line
SET @TIMESTAMP = 'C:\Pictures\Test\' +
To save the files to the location that you like
Example, if you want to save it to C:\Myimages
Then use the following line
SET @TIMESTAMP = 'C:\Myimages\' +
This worked GREAT for me. Thanks! :D
ReplyDeleteThere are a couple of minor inconsistencies in the SQL that make reading this harder for the novice. The @SQLIMG variable is declared but never used save for printing (as a debug step?) inside the while loop. But since no value is ever defined to it, it will only show up as NULL.
ReplyDeleteI would have declared @SQLIMG as VARBIN and used that instead of @IMG_PATH to store the binary in the database because it is more descriptive of the value that is being stored. Likewise, I would have used something like @FILEPATH instead of @TIMESTAMP for the name of the exported files.
But the rest of the code is pretty clean and straightforward to follow. And it helped me quickly figure out how to do something that my boss thought would take a full man day. So thanks very much!
Regards,
Lee M.
Glad that it helped you and thanks for your suggestions.
DeleteFrankly I got this code from the following source.
http://www.sqlservercentral.com/Forums/Topic693856-338-1.aspx
But thought it would help others.
HI can u help me i have used your code and image has created but not opening
ReplyDeleteYou mean, you were able to extract the image but the image won't open? If yes then that is beyond the scope of SQL Server. I would suggest you use diff clients like Picassa or Paint to open the image.
Delete