title: SQL Server BLOB image column - extracting with BCP queryout - corrupted files AND bug
body: <p>I need to export pdf and image files from a column in SQL Server 2012. Jonas's 3-step process in "<a href="https://dba.stackexchange.com/questions/80817/how-to-export-an-image-column-to-files-in-sql-server">How to export an image column to files in sql server</a>" is the clearest instruction I've found. I did everything exactly as he stated. Two notes:</p>
  
  <ol>
  <li>Where he says "your_db" in the BCP statement, you need database_name.schema_name.table_name.</li>
  <li>No line breaks are allowed. </li>
  </ol>
  
  <p>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.</p>
  
  <p>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 "<a href="https://stackoverflow.com/questions/27301763/sql-server-bcp-export-corrupted-file">SQL Server BCP export corrupted file</a>" 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:</p>
  
  <pre><code>C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn&gt;bcp CentricityPM.dbo.PatientProfileAttachment format nul -T -n -f C:\bcpdir\bcpfile.fmt
  </code></pre>
  
  <p>Then I edited and re-saved the format file as per Conor's post. I ran my BCP query:</p>
  
  <pre><code>EXEC master ..xp_cmdshell 'BCP "SELECT data FROM CentricityPM.dbo.PatientProfileAttachment WHERE PatientProfileid = ''11568'' AND type = ''pdf'' " queryout "C:\exportdir\testfile.pdf" -T -N'
  </code></pre>
  
  <p>The error:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Jon of All Trades noted in "<a href="https://stackoverflow.com/questions/23277065/bcp-error-columns-may-be-skipped-only-when-copying-into-the-server">BCP Error: columns may be skipped only when copying into the Server</a>" that this is <a href="https://connect.microsoft.com/SQLServer/feedback/details/584001/bcp-table-out-with-format-file-does-not-work-if-file-specifies-less-columns-than-source-table" rel="nofollow noreferrer">a Microsoft bug reported on 8/6/2010</a>. 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. </p>
  
  <p>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: </p>
  
  <pre><code>C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn&gt;bcp CentricityPM.dbo.PatientProfileAttachment format nul -T -n -f C:\bcpdir\bcpfile.fmt
  </code></pre>
  
  <p>Here's the format file it gave me:</p>
  
  <pre><code>11.0
  1
  1       SQLIMAGE            4       0       ""   1     data         ""
  </code></pre>
  
  <p>And here are the edits I made - I changed the data type as TT suggested below, and changed the 4 to a 0:</p>
  
  <pre><code>11.0
  1
  1       SQLBINARY            0       0       ""   1     data         ""
  </code></pre>
  
  <p>I ran my query:</p>
  
  <pre><code>EXEC master ..xp_cmdshell 'BCP "SELECT data FROM CentricityPM.dbo.TempImageFour" queryout "C:\exportdir\testfile.pdf" -T -fC "C:\bcpdir\bcpfile.fmt" '
  </code></pre>
  
  <p>It ran with no errors... but the file is still corrupted.</p>
  
  <p>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 <a href="https://sqlblobextractor.codeplex.com/" rel="nofollow noreferrer">https://sqlblobextractor.codeplex.com/</a> early on, with no success.</p>