6

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:\foty\ch6_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:\test\file.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?

user3494351
  • 127
  • 1
  • 7

3 Answers3

11

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
Conor
  • 553
  • 1
  • 7
  • 20
  • If you are running into I/O issues regarding the `.fmt` file, make sure that you have added n newline at the end of the file! The examples above don't have them. Besides that, the solution works! – avn Apr 07 '16 at 09:54
  • How to **Change that sucker to a 0** – Pரதீப் May 14 '16 at 07:10
  • @avn how do you mean add n at the end of file? where exactly? – Roxy'Pro Oct 23 '16 at 10:34
  • @Conor I did next, I deleted all my exported images, created file, changed 8 to 0, saved file, and runned bcp script again, but I'm facing issue I can not preview any of my images..even if I created that formatfile.fmt into the folder with images.. – Roxy'Pro Oct 23 '16 at 10:44
  • @Roxy'Pro, sorry, that was a typo, you need to make sure there is a blank line at the end of your file! – avn Oct 24 '16 at 12:27
  • I couldn't output a single image fields through BCP (not allowed - only allowed for import using format file), but this still helped me get to the bottom of the "corrupting" prefix. Could be result of the SQL version being run. – Anthony Horne Nov 04 '20 at 17:41
  • 1
    @AnthonyHorne I wrote this answer several years ago, but I can confirm that I created this format file in SQL Server 2012. Perhaps the ability to auto generate it was removed in later versions. – Conor Nov 12 '20 at 17:30
2

BCP is adding informations to his file. Just few data, but you are not exporting just a jpg file.

You say 167 KB, but watch the real bytes, not the rounded dimension. There will be a difference.

You cannot export the image via BCP.

user_0
  • 3,173
  • 20
  • 33
  • OK so here is the thing. If i run bcp from command line without format specified (so no -n) im being asked 4 questions: file storage type, prefix-length, length of field data and at the end if i want to save that info in file. If i choose as prefix-length 0 and length of field 0 and at the end i answer no its being saved great and can be opened normally. However how can i do it woithout being prompted for those answers?;/ – user3494351 Dec 08 '14 at 09:45
  • 1
    @user_0 this is not true, you can export the image you just have to set the format file. See my answer below. – Conor Aug 06 '15 at 19:43
2

OK so i solved the issue. Format file has to be added using -f and path to the file. It can be create by running bcp without any format and order it to save format file to disk. Then we can use this format file so its no longer needed to answer those questions, and file itself has no additional data and can be opened without problems

user3494351
  • 127
  • 1
  • 7