1

I need to export pdf and image files from a column in SQL Server 2012. Jonas's 3-step process in "How to export an image column to files in sql server" is the clearest instruction I've found. I did everything exactly as he stated. Two notes:

  1. Where he says "your_db" in the BCP statement, you need database_name.schema_name.table_name.
  2. No line breaks are allowed.

I was able to export files one at a time after this, but they were corrupt. The files were slightly smaller (by 1-15 KB) than the actual working PDFs that I can access through the UI.

This turned out to be a format issue - if you're not exporting XML files, you have to create a special format file. There's a great solution by Conor, in "SQL Server BCP export corrupted file" that tells how to create a format file and reference it in your BCP query. First I created the format file from the command line:

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn>bcp CentricityPM.dbo.PatientProfileAttachment format nul -T -n -f C:\bcpdir\bcpfile.fmt

Then I edited and re-saved the format file as per Conor's post. I ran my BCP query:

EXEC master ..xp_cmdshell 'BCP "SELECT data FROM CentricityPM.dbo.PatientProfileAttachment WHERE PatientProfileid = ''11568'' AND type = ''pdf'' " queryout "C:\exportdir\testfile.pdf" -T -N'

The error:

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Host-file columns may be skipped only when copying into the Server
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Unable to resolve column level collations
NULL
BCP copy out failed

Jon of All Trades noted in "BCP Error: columns may be skipped only when copying into the Server" that this is a Microsoft bug reported on 8/6/2010. He suggested creating a table with the right number of columns. I created a table with one column and one row of my data (?!) which Conor had actually referenced in his post but I didn't really get it until this point.

Please note, this is NOT useful to me, because I need not only the data, but a way to identify it (I have the name I want for each file stored in another column). But I gave it a try anyway - I re-ran the bcp format file: 

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn>bcp CentricityPM.dbo.PatientProfileAttachment format nul -T -n -f C:\bcpdir\bcpfile.fmt

Here's the format file it gave me:

11.0
1
1       SQLIMAGE            4       0       ""   1     data         ""

And here are the edits I made - I changed the data type as TT suggested below, and changed the 4 to a 0:

11.0
1
1       SQLBINARY            0       0       ""   1     data         ""

I ran my query:

EXEC master ..xp_cmdshell 'BCP "SELECT data FROM CentricityPM.dbo.TempImageFour" queryout "C:\exportdir\testfile.pdf" -T -fC "C:\bcpdir\bcpfile.fmt" '

It ran with no errors... but the file is still corrupted.

Can anyone see anything I've done wrong? Does anyone know a workaround for the one-column-only bug? Or if you know of a working tool that will do this for me, that'd be great too. I tried https://sqlblobextractor.codeplex.com/ early on, with no success.

Community
  • 1
  • 1
Amy C
  • 43
  • 1
  • 6
  • If my answer doesn't help you, please edit your question and add the contents of the `fmt` file. – TT. Jan 15 '16 at 08:29
  • Thank you for your answer TT. I tried what you said - both the -fC and the -C RAW options, and I changed the datatype to SQLBINARY, but I'm still getting a corrupted file. I added the format file to my question above - original and edited. – Amy C Jan 15 '16 at 17:07
  • So IIUC, you no longer get any errors from running BCP, but your exported file is corrupt? Try casting the image to VARBINARY(MAX) in the query: `"SELECT CAST(data AS VARBINARY(MAX)) FROM CentricityPM.dbo.TempImageFour"` in your BCP command. – TT. Jan 15 '16 at 18:16
  • Now your format file looks identical to what I used to export zip files from database (apart from the version in the first line, and there are spaces to delimit the fields in the format file instead of tabs) – TT. Jan 15 '16 at 18:22
  • If all else fails, maybe you could try the suggestions in this [SO thread](http://stackoverflow.com/a/9127325/243373) – TT. Jan 15 '16 at 18:34
  • @TT - I tried your casting suggestion and no change. Yes, the BCP query runs with no errors, and the file writes, but it's corrupt, and 13 KB smaller than the working PDF. All of the other references I find to corrupt files say that the extracted file is slightly larger than the original, but that isn't the case with mine. I am going to try your last suggestion. – Amy C Jan 15 '16 at 20:56
  • With the updated format file, have you tried adding `-n` option again? I'm just guessing here... Anyway, I'm gonna try a proof-of-concept script tomorrow (GMT+1) see if I can show something that works and that you can test. – TT. Jan 15 '16 at 21:16
  • I've created a chat room here to provide you with an example of what works for me [here](http://chat.stackoverflow.com/rooms/100829/bcp-command-works-fine-heres-a-test) – TT. Jan 16 '16 at 07:35
  • Did you follow the script from the discussion room? Did that shed some light on your issue? – TT. Jan 21 '16 at 22:02
  • I appreciate your help, TT. I've been working on other projects and this one was scrapped. This is medical data, stuffed into a system that was never meant to hold it. I was trying to migrate some of it, but my CIO decided to start fresh with the new system. – Amy C Feb 01 '16 at 16:39
  • 1
    You are welcome of course. `BCP` is a guilty pleasure of mine ;-). – TT. Feb 01 '16 at 16:49

1 Answers1

1

You are using parameter -f "C:\bcpdir\bcpfile.fmt" but from my experience that should be -fC "C:\bcpdir\bcpfile.fmt". To be honest I don't remember anymore why... I once made something similar to export files (.zip) from database and my command has -fC parameter for the export file. I whish I could give you a proper explanation. Anyway, HTH.

Try the following command:

EXEC master..xp_cmdshell 'BCP "SELECT data FROM CentricityPM.dbo.TempImageFour" QUERYOUT "C:\exportdir\testfile.pdf" -T -fC "C:\bcpdir\bcpfile.fmt"'

An alternative is to specify the -C RAW option. This specifies that no conversion is done from one code page to another.

EXEC master..xp_cmdshell 'BCP "SELECT data FROM CentricityPM.dbo.TempImageFour" QUERYOUT "C:\exportdir\testfile.pdf" -T -f "C:\bcpdir\bcpfile.fmt" -C RAW'

Also, make sure that your format file has SQLBINARY as data type for your column.

TT.
  • 15,774
  • 6
  • 47
  • 88
  • I am really confused what path or filename I should give here `-fC "C:\bcpdir\bcpfile.fmt"` – Pரதீப் May 07 '16 at 02:52
  • @Prdp `-f format_file: Specifies the full path of a format file.`. You can read this [here](https://msdn.microsoft.com/en-us/library/ms162802.aspx#Anchor_1). To use a format file, you first need to create one. You can read about that [here](https://msdn.microsoft.com/en-us/library/ms191516.aspx). – TT. May 07 '16 at 06:48
  • @Prdp A format file describes how data is to be output if you export data from SQL Server, or if you input data how the data is formatted. For instance it describes what the field separator is (eg tab or comma separated fields). It describes what the row separator is (eg `/r/n` between rows). It describes the character format for VARCHAR fields (eg `Latin1_General_CI_AI`). You can generate such a format file by running the [BCP Utility](https://msdn.microsoft.com/en-us/library/ms162802.aspx) with specific parameters (`format nul -f '...'`). – TT. May 14 '16 at 06:24