How do I use BCP or Sql Server Management Studio to get BLOB data out of Sql Server?

Question

I'm sorry if this question has been asked already, but I couldn't find it anywhere. I have a table that stores files as BLOBS. The column that holds the file is an image datatype. I would like to be able to extract the binary data out of the column and turn it in to an actual file. I would ideally like to be able to do this with BCP or management studio if possible.

I have tried BCP, but for some reason when I try and pull out an office document Word thinks it's corrupt. Here's what I've tried so far (obviously the values have been changed to protect the innocent :):

bcp "select document_binary_data from database where id = 12345" queryout "c:\filename.doc" -n -S server -U username -P password

This isn't working though? Any thoughts?

Edit Turns out you don't need the -n native flag. Also, BCP tries to include a 4 byte prefix by default on the image column - you actually want this set to 0.

bcp "select document_binary_data from database where id = 12345" queryout "c:\filename.doc" -S server -U username -P password

Enter the file storage type of field document_binary [image]:
Enter prefix-length of field document_binary [4]: 0
Enter length of field document_binary [0]:
Enter field terminator [none]:

Edwards

Answer 1

I'm answering my own question since I'm getting annoyed with SO telling me to setup a bounty

Turns out you don't need the -n native flag. Also, BCP tries to include a 4 byte prefix by default on the image column - you actually want this set to 0.

bcp "select document_binary_data from database where id = 12345" queryout "c:\filename.doc" -S server -U username -P password

Enter the file storage type of field document_binary [image]:
Enter prefix-length of field document_binary [4]: 0
Enter length of field document_binary [0]:
Enter field terminator [none]:

Edwards

Answer 2

If you can use C# / .NET code to do this, the following KB article may come in handy:

http://support.microsoft.com/kb/317016

Apparently you can do something like this with BCP and a format file, but IIRC the format file has to be pre-populated with the exact number of bytes it is expecting to pull from the column, which makes it quite impractical.

Another option you might choose is to use FILESTREAM in 2008 or, if you are not planning to migrate to 2008 anytime soon, store the documents on the file system and a pointer to them in the database. Yes there are pros and cons to this, but it's the way we've chosen in all projects to date.

Beckmann

I Aaron, we actually do store all of our documents on the filesystem for 95% of our application. There is one bit that still uses blobs though :( – Edwards

You don't need to pre-populate the format file with the field length; the following works for me (line breaks after 9.0 and the following 1): 9.0 1 1 SQLBINARY 0 0 "" 1 col1 "" – Nebulous

@aaron - apparently there are a lot of problems with exporting varbinary (max) to file. ( I just finished doing it with c# - after 3 hours trying to do it with BCP). ( the problem is with refix-length of field document_binary which should be set to 0 - but it requires user interaction.) It can also be done with FMT file. but what if I dont have access to FMT file ? is there a way to "inline use" the fmt content within the bcp command ? – Tandem