EDIT: Wow... This was boneheaded of me. I didn't see there were two other pages after the first, in which DE's questioned were eventually answered. Leaving the rest of my post here since I can't delete it outright...

Apologies for the necroposting, but this thread came up a handful of times when I was googling around trying to find a solution to the very same problem.

In short, DeafEater's problem is real, not imagined. I've had the same problem, and it doesn't matter whether I'm connecting to SQL Server 2000, 2005, or 2008. It doesn't matter what version of the client tools I'm using.

In my case, I'm importing and exporting binary files, mostly ZIPs. I'm importing files into the table like so:

CREATE TABLE FileStore (

[id] [int] IDENTITY(1,1) NOT NULL,

[filename] [varchar](255) NULL,

[created] [datetime] NULL,

[filedata] [varbinary](max) NULL,

)

insert FileStore (filename, created, filedata) select '$(_FILENAME)', GETDATE(), BulkColumn from Openrowset ( Bulk '$(_FILENAME)', Single_Blob) as filedata

Originally, I exported ZIP files from the database with:

bcp "select top 1 filedata from dbiutil.dbo.FileStore order by created desc" queryout test01.zip -S fssrv -T -n

Which appeared to work fine, but test01.zip was always four bytes larger than it should be- and WinZip wasn't able to open it. Later, I figured out that Info-ZIP (zip.exe and unzip.exe) and 7zip (7za.exe) had no problems extracting the ZIP. However, Info-ZIP gives this warning:

Archive: test0.zip

warning [test0.zip]: 4 extra bytes at beginning or within zipfile

(attempting to process anyway)

creating: test01/

inflating: test01/a

inflating: test01/b

Naturally, this stumped me. After running into a few forum threads here and elsewhere, I tried doing the BCP without the -n (native type) switch...

c:\bcptesting>bcp "select top 1 filedata from dbiutil.dbo.FileStore order by created desc" queryout test02.zip -S fssrv -T

Enter the file storage type of field filedata [image]:

Enter prefix-length of field filedata [4]: 0

Enter length of field filedata [0]:

Enter field terminator [none]:

Do you want to save this format information in a file? [Y/n] y

Host filename [bcp.fmt]:

Starting copy...

1 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.): total 6000

This time, test01.zip had no extra bytes, and I was able ot open it in WinZip, Info-ZIP, and 7zip without errors or warnings. Indeed, the md5sum hash of the test02.zip that I exported with this method was the same as the original file.

It should be obvious to anyone reading this that those four extra bytes is the "prefix-length of field filedata" mentioned above. By specifically telling bcp to use 0 things are imported and exported without any corruption or added bytes. Since running through this interactively is hardly what we want, just take the resulting bcp.fmt and use it for any future exports...

c:\cbtesting>bcp "select top 1 filedata from dbiutil.dbo.FileStore order by created desc" queryout test03.zip -S fssrv -T -f bcp.fmt

Starting copy...

1 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.): total 5922

Again, test03.zip has the same md5sum hash as the original file and test02.zip.

It's a bit tedious to have to ship an extra file like this, but at least there's some solution. It works the same way no matter what version of SQL Server you're using from 2000 on; same goes for the client tools. Microsoft forgot to add a parameter for bcp.exe for specifying the "prefix-length of field filedata," forcing you to run through the manual process once first.

BCP doesn't have a bug, it's just poorly designed. 😛

Aaron