CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012blobbcp
Amy C
Amy C

Reputation: 43

SQL Server BLOB image column - extracting with BCP queryout - corrupted files AND bug

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.

Upvotes: 1

Views: 7858

Answers (1)

TT.
TT.

Reputation: 16137

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.

Upvotes: 1

Related Questions