SQL Server BCP export corrupted file?

Question

i have a small problem with BCP functionality in SQL Server 2012. The things is: im loading .jpg image (167KB in size) using below command:

INSERT [tabela_testowa] ( Data ) SELECT * FROM OPENROWSET (BULK N'C:fotych6_MagicShop.jpg', SINGLE_BLOB) a;

and then im trying to export it back to disk using:

BCP "SELECT data FROM tabela_testowa WHERE ID = 1" queryout "C:estfile.jpg" -T -n -d test

File gets saved on disk no problem, size is also 167 KB but.. it cant be opened like the original copy. I dont know whatever some parameter is wrong in BCP export? Or maybe it gets corrupted at import stage? Anyone had similiar problems?

Answer 1

Thank god, thanks to @user_0 answer and @user3494351's cryptic answer and comment and this ancient forum post I finally figured this out after several hours of banging my head against the wall.

The issue is that BCP likes to add an extra 8 bytes to the file by default. This corrupts the file and makes it unable to be opened if you just use the native -n flag.

However, BCP allows you to specify a format file as output that can allow you to tell it not to add the extra 8 bytes. So I have a table I created (to be used in a cursor) in SQL Server that only has ONE ROW and ONE COLUMN with my binary data. Table must exist when you run the first command.

In command line first you need to do this:

bcp MyDatabase.MySchema.MyTempTable format nul -T -n -f formatfile.fmt

This creates formatfile.fmt in the directory you are in. I did on E: drive. Here's what it looks like:

10.0
1
1       SQLBINARY           8       0       ""   1     MyColumn             ""

That 8 right there is the variable that bcp says how many bytes to add to your file. It is the bastard that is corrupting your files. Change that sucker to a 0:

10.0
1
1       SQLBINARY           0       0       ""   1     MyColumn             ""

Now just run your BCP script, drop the -n flag and include the -f flag:

bcp "SELECT MyColumn FROM MyDatabase.MySchema.MyTempTable" queryout "E:MyOutputpath" -T -f E:formatfile.fmt