retrive images from sql table to file

  • sam-433147

    Ten Centuries

    Points: 1221

    is there a way to retrive images from sql table to file byTSql procedure?



  • Jan-155192


    Points: 2613

    Please don't post the same questions in multiple threads.


    Copy in

    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:



    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,


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

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