Open Blob fields using Openrowset in SQLSERVER 2008 R2

Question

I need help to read a fileobject from SQLServer2008 R2 using the Openrowset, i can write a File to a Blob column like this:

INSERT INTO myTable(FileName, FileType, Document) 
   SELECT 'Text1.txt' AS FileName, 
      '.txt' AS FileType, 
      * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;

but how do i read it back and write it to the disk?

thank you

score 0 · Accepted Answer · answered May 22 '11 at 16:34

You can use the bcp utility in conjunction with the queryout argument to save a blob to disk.

bcp "select datei 
     from   Adventureworks.Person.Address 
     WHERE  addressid=1 " 
queryout "c:\TestOut.doc" -T -n -Slocalhost

There are some good examples at the bcp reference page, as well as plenty of command line options.

Open Blob fields using Openrowset in SQLSERVER 2008 R2

1 Answers1