3

I have a SQL Server 2008 database, with a VARCHAR(MAX) FILESTREAM column. I can load a record into it using this syntax:

`INSERT INTO DocumentRepository(DocumentExtension, DocumentName, Document) 
 SELECT
 'doc' AS DocumentExtension
 , 'Hamlet.doc' AS DocumentName
 , * FROM OPENROWSET(BULK 'D:\Docs\Hamlet.doc', SINGLE_BLOB) 
   AS Document;`

This loads my word document just fine. What I'd like to know is how to extract the data back to disk using SQL Server supplied tools. Might be T-SQL, bcp, etc, don't really care I would just like to know a way to do this without resorting to having to write a .NET application.

I've experimented with bcp, using the -c, -n, -N, and -w switches and it will extract the document but it's not formatted correctly. When I open it in Word there is a lot of binary gibberish at the beginning, and while the text is there all formatting is gone.

Thanks!

3
  • You'll probably want to take this over to StackOverflow.com.
    – squillman
    May 17 '09 at 22:00
  • agreed. SO is the correct site, for this question.
    – Pure.Krome
    May 17 '09 at 23:39
  • I've come to the conclusion that UndertheFold is correct, there is no way to do it without resorting to .Net. Been searching for several days and have found no satisfactory answer. SSIS may let you but I have not explored it (yet) one way or the other.
    – user2867
    May 19 '09 at 19:10
2

Great question - Basically you can't - you could select the path and access the file directly from the file system

But from Books online you will notice the operations are

  • insert
  • update
  • query
  • back up

FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.

5

I feel a bit embarrassed to admit this, given that I wrote the FILESTREAM whitepaper for Microsoft - but I don't know the answer. My feeling is the same as UndertheFold's - you can't do it. I had a poke about to see if I could find any way of doing it with SSIS but I couldn't.

0
0

As UndertheFold states, you can't.

However, it is important to realize that renaming or deleting files directly in the filesystem will corrupt your db (Source). I'm not sure about editing the files, but personally I wouldn't risk it.

0

Did you try using the "Export Column" task in SSIS?

See a detailed example here

MS documentation on Export Column

1
  • Welcome to Server Fault! Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference. Dec 6 '16 at 13:35
-1

There is a solution here that should work. Have not tested it myself but looks valid.

http://www.connectsql.com/2010/12/ssis-filestream-data-export-and-import.html

1
  • 1
    Please describe the solution here, or your answer won't be useful later after the linked article goes away. Mar 7 '18 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.