none
Dumping varbinary(max) column values to files on harddisk using a SQL script

    Question

  • Hi

     

    I have an SQL Server 2005 database that has a table with images(.jpg) stored as varbinary(max).

     

    I need a TSQL script to dump each image to a folder and name it based on another column called [Code].

     

    Can anyone help?

     

    Thanks a lot

    Iyad

    Monday, June 23, 2008 8:11 AM

Answers

  • BCP does not make the newly created file accessible to the file system.  The only true way to reproduce the image is to use CLR or .NET file stream binary read/write.

     

    Below is a sample of an import and export.  You will note that you cannot open the image via Windows Explorer or even in MS paint.

     

    Code Snippet

    CREATE TABLE IMG(

    ImageBinary varbinary(max),

    )

    go

     

    insert into img

    select

    BulkColumn from

    Openrowset( Bulk 'C:\Sunset.jpg', Single_Blob) as photo

    go

     

    select *

    from img

    go

     

    declare @SQLcommand varchar(8000)

    set @SQLcommand = 'bcp "SELECT ImageBinary FROM Test.dbo.IMG" queryout "C:\Sunset_Mod.jpg" -T -n -S A70195\dev'

    exec xp_cmdshell @SQLcommand

    go

     

    drop table img

    go

     

     

    Here is a sample on using file stream binary write:

    http://aspnet.4guysfromrolla.com/articles/120606-1.aspx

    Monday, June 23, 2008 2:33 PM

All replies

  • If you want a strictly TSQL implementation, you will have to use the OLE Automation routines to create the output, but you can also do this utilizing SQL CLR.  The following post should give you an idea of how to use the OLE Automation to do this:

     

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101754

     

    You can also use bcp to dump the data off to a file if you have th xp_cmdshell enabled on the 2005 instance:

     

    Code Snippet

    declare @SQLcommand nvarchar(max)

    set @SQLcommand = 'bcp "SELECT imagedaa FROM myDatabase.dbo.Imaging" queryout "imagename.jpg" -T -n -S ServerName'

     

    exec xp_cmdshell @SQLcommand

    go

     

     

     

     If you want to look at CLR to do it, let me know and I will post an example of how.

    Monday, June 23, 2008 12:55 PM
  • I was going down the same road as Jonathan.  Here is what I came up with.

     

     

    Code Snippet

    DECLARE @FileName varchar(50)

    Declare @bcpCommand varchar(2000)

    Declare @Code varchar(50)

    Declare @RowID int

    DECLARE Image_cursor CURSOR FOR

    SELECT RowID, Code

    FROM Table1

     

    OPEN Image_cursor

    FETCH NEXT FROM Image_cursor

    INTO @RowID, @Code

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @FileName = 'C:\Test\' + @Code + '.jpg'

    SET @bcpCommand = 'bcp "SELECT ImageField From Test.dbo.Table1 Where RowID = ' + str(@RowID) + '" queryout "'

    SET @bcpCommand = @bcpCommand + @FileName + '" -T -c'

    EXEC xp_cmdshell @bcpCommand

    -- Get the next Code.

    FETCH NEXT FROM Image_cursor

    INTO @RowID, @Code

    END

    CLOSE Image_cursor

    DEALLOCATE Image_cursor

     

     

    Monday, June 23, 2008 1:08 PM
  • BCP does not make the newly created file accessible to the file system.  The only true way to reproduce the image is to use CLR or .NET file stream binary read/write.

     

    Below is a sample of an import and export.  You will note that you cannot open the image via Windows Explorer or even in MS paint.

     

    Code Snippet

    CREATE TABLE IMG(

    ImageBinary varbinary(max),

    )

    go

     

    insert into img

    select

    BulkColumn from

    Openrowset( Bulk 'C:\Sunset.jpg', Single_Blob) as photo

    go

     

    select *

    from img

    go

     

    declare @SQLcommand varchar(8000)

    set @SQLcommand = 'bcp "SELECT ImageBinary FROM Test.dbo.IMG" queryout "C:\Sunset_Mod.jpg" -T -n -S A70195\dev'

    exec xp_cmdshell @SQLcommand

    go

     

    drop table img

    go

     

     

    Here is a sample on using file stream binary write:

    http://aspnet.4guysfromrolla.com/articles/120606-1.aspx

    Monday, June 23, 2008 2:33 PM
  • It's Ok with Image,txt,pdf files but when try to export a Zip file, It's exported to file sytem well, but the problem is I'm unable to extract them gettting message "Zip file is invalid or corrupted" .

    Here is the T-SQL that i Executing:

    bcp "SELECT [File] FROM OrderManagementUtility.dbo.OrderBulletinFiles where orderid=3 and filename='CustomPages.zip'" queryout "E:\OrderManagementUtility\TempExported\CustomPages.zip" -n -S STAGE2 -U **** -P ******

    What should I do so that Exported file Should be Extracted properly?


    Thanks
    Monday, August 17, 2009 12:45 PM
  • You can't export properly to a file without a format file which specifies that you do not want the data size in the column exported. Below is my sample. Note that I build the contents of the format file in tsql and write it to the same directory as the export file directory and then remove it when done. A bit of a kludge, but it works well:

    CREATE PROCEDURE dbo.p_ExportUserImage (
    @userName VARCHAR(80),
    @pathToFile VARCHAR(300)
    ) AS
    BEGIN
    DECLARE @dir VARCHAR(100)
    DECLARE @formatFile VARCHAR(100)
            DECLARE @userID INT
    SELECT @userID = UserID FROM dbo.Users WHERE UserName = @userName

    SELECT @dir = REVERSE(SUBSTRING(REVERSE(@pathToFile),CHARINDEX('\', REVERSE(@pathToFile), 1) + 1,LEN(@pathToFile) - CHARINDEX('\', REVERSE(@pathToFile),1)))
    SELECT @formatFile = @dir+'\image_export.fmt'

    DECLARE @command NVARCHAR(4000)
    -- First write a format file which allows us to dump the image column without column 
    SELECT @command = 'echo 9.0 >> '+ @formatFile
    EXEC xp_cmdshell @command
    SELECT @command = 'echo 1 >> '+ @formatFile
    EXEC xp_cmdshell @command
    SELECT @command = 'echo 1       SQLBINARY     0       0       ""   1     blob_data              "" >> '+ @formatFile
    EXEC xp_cmdshell @command

    SELECT @command = 'bcp '+
    '"SELECT UserImage '+
    'FROM dbo.Users '+
    'WHERE UserID= '+CONVERT(VARCHAR(10), @userID)+'" '+
    'queryout "'+@pathToFile+'" -f "'+@formatFile+' -T -SGARDEVSRV07\GARDEVSQL3_1'
    PRINT @command
    EXEC xp_cmdshell @command

    SELECT @command = 'del '+@formatFile
    EXEC xp_cmdshell @command
    END
    • Proposed as answer by bpeikes Tuesday, July 24, 2012 3:13 PM
    Tuesday, July 24, 2012 3:13 PM