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]:

Solution

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]:

Comments

[Eric]

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 :(

[Geoff]

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 ""

[Royi Namir]

@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 ?