如何使用BCP或Sql Server Management Studio从SQL Server中获取BLOB数据? [英] How do I use BCP or Sql Server Management Studio to get BLOB data out of Sql Server?

问题描述

很抱歉，如果您已经问过这个问题，但是我找不到任何地方.我有一个表，将文件存储为BLOBS.存放文件的列是图像数据类型.我希望能够从列中提取二进制数据并将其转换为实际文件.理想情况下，我希望能够与BCP或Management Studio做到这一点.

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.

我尝试过BCP，但是由于某些原因，当我尝试取出Office文档时，Word认为它已损坏.到目前为止，这就是我尝试过的方法(显然，已经更改了值以保护无辜的人:):

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?

编辑原来，您不需要-n本机标志.另外，BCP尝试默认在图像列上包含4字节前缀-您实际上希望将此设置为0.

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

推荐答案

我正在回答自己的问题，因为我对SO告诉我设置赏金感到恼火

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

原来，您不需要-n本机标志.另外，BCP尝试默认在图像列上包含4字节前缀-您实际上希望将此设置为0.

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

这篇关于如何使用BCP或Sql Server Management Studio从SQL Server中获取BLOB数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！