如何将我们的所有图像从SQL Server 2008中的VARBINARY（MAX）字段转储到文件系统？

I have a table which has an IDENTITY field and a VARBINARY(MAX) field in it. Is there any way I could dump the content of each VARBINARY(MAX) field to the filesystem (eg. c:\temp\images)?

我有一个表,其中包含IDENTITY字段和VARBINARY(MAX)字段。有没有办法可以将每个VARBINARY(MAX)字段的内容转储到文件系统(例如c:\ temp \ images)?

Table schema:

PhotoId INTEGER NOT NULL IDENTITY
Image VARBINARY(MAX) NOT NULL
DateCreated SMALLDATETIME

Output:

c:\temp\images\1.png
c:\temp\images\2.png
c:\temp\images\3.png
... etc...

What is the best way to approach this?

解决这个问题的最佳方法是什么?

2 个解决方案

#1

I've figured it out thanks to this post ...

我已经弄明白了,感谢这篇文章......

SET NOCOUNT ON

DECLARE @IdThumbnail INTEGER,
        @MimeType VARCHAR(100),
        @FileName VARCHAR(200),
        @Sqlstmt varchar(4000)


DECLARE Cursor_Image CURSOR FOR
    SELECT a.IdThumbnail
    FROM tblThumbnail a
    ORDER BY a.IdThumbnail

OPEN Cursor_Image
    FETCH NEXT FROM Cursor_Image INTO @IdThumbnail

    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Generate the file name based upon the ID and the MIMETYPE.
        SELECT @FileName = LTRIM(STR(@IdThumbnail)) + '.png'

        -- Framing DynamicSQL for XP_CMDshell            
        SET @Sqlstmt='BCP "SELECT OriginalImage 
                      FROM Appian.dbo.tblThumbnail 
                      WHERE IdThumbnail = ' + LTRIM(STR(@IdThumbnail)) +
                      '" QUERYOUT c:\Temp\Images\' + LTRIM(@FileName) + 
                      ' -T -fC:\Temp\ImageFormatFile.txt'
        print @FileName
        print @sqlstmt

        EXEC xp_cmdshell @sqlstmt
        FETCH NEXT FROM Cursor_Image INTO @IdThumbnail
    END

CLOSE Cursor_Image
DEALLOCATE Cursor_Image

Please note -> u need to have a format file, for the BCP command. This is the content of the file and i've placed it in c:\Temp (as noted in the BCP commandline above).

请注意 - >你需要有一个格式文件,用于BCP命令。这是文件的内容,我把它放在c:\ Temp中(如上面的BCP命令行中所述)。

10.0
1
1       SQLIMAGE            0       0       ""   1     OriginalImage                      ""

Final note about that format file .. there HAS TO BE A NEW LINE AFTER THE LAST LINE. otherwise u'll get an error.

关于那个格式文件的最后注释..在最后一行之后有一个新线。否则你会收到错误。

Enjoy!

#2

The easiest (for me) would be to write a little .NET application that dumped the varbinary fields to a file.

最简单的(对我而言)是编写一个将varbinary字段转储到文件的.NET应用程序。

If you are opposed to working in .NET, then you should (I've not tested this) be able to create a new column of type "VARBINARY(MAX) FILESTREAM". Be sure you also have a the corresponding "ROWGUIDCOL" column. Then you can (in theory) do something like:

如果您反对在.NET中工作,那么您应该(我没有测试过这个)能够创建一个类型为“VARBINARY(MAX)FILESTREAM”的新列。确保您还有相应的“ROWGUIDCOL”列。然后你可以(在理论上)做类似的事情:

UPDATE table SET varfilestream_col = varbinary_col

UPDATE表SET varfilestream_col = varbinary_col

Hope that works for ya.

希望对你有用。

#1