SQL Server BLOB映像列-使用BCP查询输出提取-损坏的文件和错误 [英] SQL Server BLOB image column - extracting with BCP queryout - corrupted files AND bug

问题描述

我需要从SQL Server 2012的一列中导出pdf和图像文件."

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. 他在BCP语句中说"your_db"的地方，您需要database_name.schema_name.table_name.
2. 不允许换行.

此后，我一次可以导出一个文件，但是它们已损坏.这些文件比我可以通过用户界面访问的实际工作PDF稍小(1-15 KB).

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.

事实证明这是一种格式问题-如果您不导出XML文件，则必须创建一个特殊的格式文件. Conor在" SQL Server BCP导出损坏的文件"中有一个很好的解决方案.告诉您如何创建格式文件并在BCP查询中引用它.首先，我从命令行创建了格式文件:

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

然后，根据Conor的帖子，我编辑并重新保存了格式文件.我运行了BCP查询:

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'

错误:

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

《万事通》中的" 2010年8月6日报告的Microsoft错误.他建议创建一个具有正确列数的表.我创建了一个包含一列和一行数据(?！)的表，康纳尔实际上在他的帖子中引用了该表，但直到现在我才真正得到它.

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.

请注意，这对我没有用，因为我不仅需要数据，而且需要标识数据的方式(对于存储在另一列中的每个文件，我都有想要的名称).但无论如何，我还是尝试了一下-我重新运行了bcp格式文件:

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         ""

这是我所做的编辑-我按照以下建议的TT更改了数据类型，并将4更改为0:

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         ""

我运行了查询:

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.

有人可以看到我做错了什么吗?有谁知道仅一栏错误的解决方法?或者，如果您知道可以为我完成此工作的工具，那也很好.我很早就尝试 https://sqlblobextractor.codeplex.com/，但没有成功.

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.

推荐答案

您正在使用参数-f "C:\bcpdir\bcpfile.fmt"，但是根据我的经验，应该为-fC "C:\bcpdir\bcpfile.fmt".老实说，我已经不记得了为什么...我曾经做过一些类似于从数据库导出文件(.zip)的事情，并且我的命令具有用于导出文件的-fC参数.希望我能给您适当的解释.无论如何，HTH.

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.

尝试以下命令:

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

另一种选择是指定-C RAW选项.这指定了从一个代码页到另一个代码页的转换不会完成.

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'

此外，请确保格式文件的列的数据类型为SQLBINARY.

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

这篇关于SQL Server BLOB映像列-使用BCP查询输出提取-损坏的文件和错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！