Post reply

retriving images from sql table to files

  • May 15, 2007 at 12:59 pm

    #178518

    is there is t-sql procedure for retriving images from sql table to files?

    thanks

  • May 16, 2007 at 1:46 am

    #706685

    Sam,

    Copying in is a snap:

    UPDATE dbo.TestData

    SET binary_data = (SELECT binary_data FROM OPENROWSET(

    BULK 'c:\temp\picture.jpg', SINGLE_BLOB) AS F(binary_data))

    To copy the file out, before SQL2005 there existed a tool called TEXTCOPY.EXE. However this doesn’t exist anymore. I tried using SQLCMD, OSQL and BCP but each time the jpg got corrupted. At last I found that you could use a format file with BCP. This seems to work.

    BCP "SELECT binary_data FROM tempdb.dbo.testdata" queryout D:\temp\test.jpg -S ServerName -T -f test.fmt

    Format file contents:

    9.0

    1

    1 SQLBINARY 0 0 "" 1 col1 ""

    Of course you need to loop through your records to make sure that you copy only one picture to the output at the time!

    Hope this helps,

    Jan

  • May 18, 2007 at 8:07 pm

    #707612

    Have you tried using the 2000 version of TEXTCopy against the 2005 code?  May stil work.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply