Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
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
add comment

3 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
+1 for honesty! :) –  squillman May 18 '09 at 2:22
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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