使用 BCP 从 SQL Server 2000 导出图像列 [英] Export Image column from SQL Server 2000 using BCP

问题描述

我的任务是将一些数据从 SQL Server 2000 数据库中提取为磁盘上的平面格式.我几乎没有 SQL Server 经验.

I've been tasked with extracting some data from an SQL Server 2000 database into a flat format on disk. I've little SQL Server experience.

有一个表，其中包含存储在IMAGE"类型列中的文件，以及存储文件名的 nvarchar 列.

There is a table which contains files stored in an "IMAGE" type column, together with an nvarchar column storing the filename.

看起来表格中存储了多种类型的文件:Word docs、XLS、TIF、txt、zip 文件等.

It looks like there are numerous types of files stored in the table: Word docs, XLS, TIF, txt, zip files, etc.

我正在尝试使用 BCP 仅提取一行，执行如下操作:

I'm trying to extract just one row using BCP, doing something like this:

bcp "select file from attachments where id = 1234" queryout "c:\myfile.doc" -S <host> -T -n

这会保存一个文件，但它已损坏，我无法用 Word 打开它.当我用 word 打开文件时，我可以看到很多文本，但我也得到了很多无法呈现的字符.我在尝试提取图像文件时遇到了类似的问题，例如TIF.照片软件无法打开文件.

This saves a file, but it is corrupt and I can't open it with Word. When I open the file with word, I can see a lot of the text, but I also get a lot of un-renderable characters. I've similar issues when trying to extract image files, e.g. TIF. Photo software won't open the files.

我想我遇到了某种字符编码问题.

I presume I'm hitting some kind of character encoding problems.

我在 BCP 中使用了 -C(例如尝试 RAW)和 -n 选项，但仍然无法使其工作.

I've played around with the -C (e.g. trying RAW) and -n options within BCP, but still can't get it to work.

SQL Serer 中的表的排序规则为SQL_Latin1_General_CP1_CI_AS"

The table in SQL Serer has a collation of "SQL_Latin1_General_CP1_CI_AS"

我正在从我的 Windows 7 桌面远程运行 BCP.

I'm running BCP remotely from my Windows 7 desktop.

知道我哪里出错了吗?非常感谢任何帮助.

Any idea where I'm going wrong? Any help greatly appreciated.

推荐答案

我通过更改 BCP 在调用命令时询问您的默认选项来实现此目的:

I got this working by changing the default options which BCP asks you about when you invoke the command:

区别在于将前缀长度字段从 4 更改为 0.

The one that made the difference was changing the prefix-length field from 4 to 0.

这篇关于使用 BCP 从 SQL Server 2000 导出图像列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！