Sunday, December 11, 2011

SQL Server 2005/2008 : Export Blob images data from database

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.

8 comments:

  1. Please provide the solution if we wish to save the file with one column name available in the table having the image data.

    ReplyDelete
    Replies
    1. I didn't understand your requirement properly.

      Do 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?

      Delete
    2. 1) In the script there is line like

      SELECT 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\' +

      Delete
  2. This worked GREAT for me. Thanks! :D

    ReplyDelete
  3. There 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.

    I 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.

    ReplyDelete
    Replies
    1. Glad that it helped you and thanks for your suggestions.

      Frankly I got this code from the following source.
      http://www.sqlservercentral.com/Forums/Topic693856-338-1.aspx

      But thought it would help others.

      Delete
  4. HI can u help me i have used your code and image has created but not opening

    ReplyDelete
    Replies
    1. You 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