CODEWITHSUNDEEP
sql-servert-sqlsql-server-2000
A_M
A_M

Reputation: 7851

Export Image column from SQL Server 2000 using BCP

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.

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

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

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

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.

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

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

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

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

Upvotes: 2

Views: 5969

Answers (2)

sw0rdf1sh2318
sw0rdf1sh2318

Reputation: 11

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

after this [image] : I (enter capital "I"] 0 0 Enter save file Y

kallas.. ur file is there

Upvotes: 0

A_M
A_M

Reputation: 7851

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

BCP prompts

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

Upvotes: 5

Related Questions