SQL Server BCP将二进制文件导出到文件:文件开头的其他数据 [英] SQL Server BCP export binary to file: extra data at the begining of the file

问题描述

我尝试将此BCP使用 xp_cmdshell :

I tried to use xp_cmdshell with this BCP:

'BCP "SELECT TOP 1 Data FROM <FQDN> WHERE Name = ''<name>'' " queryout "C:\exportdir\export_data.dat" -T -n -S .\SQLEXPRESS'

但是在文件缓冲区的开头我得到了一些额外的数据.我测试了两次，两个文件都以

But I'm getting some extra data at the begining of the file buffer. I tested it twice and both files started with

BB 67 B9 00 00 00 00

BB 67 B9 00 00 00 00

我想摆脱这个问题，我尝试用-n和-w替换-N参数，但是没有运气.

I'd like to get rid of this, I tried to replace the -N parameter both with -n and -w but no luck.

推荐答案

为此，您需要使用格式文件运行导出，该文件为data字段指定前缀长度为 0 . .您可以从XML格式文件或非XML格式文件中指定.

To do this you need to run the export with a format file that specifies a prefix length of 0 for the data field. You can specify this from an XML format file or non-XML format file.

例如生成非XML格式的文件:要创建非XML格式的文件，您将使用参数format nul -f <format_file>(除了其他必需的参数)运行BCP.这将在您指定格式文件的地方创建格式文件.使用您喜欢的文本编辑器并将前缀长度更改为0.默认情况下，BCP会将其生成为非零值，并且无法单独使用带有参数的BCP将其自动设置为0.

E.g. to generate a non-XML format file: To create a non-XML format file you would run BCP with parameters format nul -f <format_file> (in addition to other required parameters). That would create the format file where you specify it. Use your favorite text editor and change the prefix length to 0. By default this will be generated by BCP as non-zero and there's no way to get it automatically to 0 using BCP with parameters alone.

在最后的导出命令中，您将引用格式文件(前缀长度为0)以原始格式导出文件.请参阅有关格式文件的文档以了解更多详细信息:创建格式文件(SQL Server)，非XML格式文件(SQL Server)等.

In the final exporting command, you would reference the format file (with 0 prefix length) to export the file in its original form. Please refer to documentation on format files for more details: Create a Format File (SQL Server), Non-XML Format Files (SQL Server) etc.

这篇关于SQL Server BCP将二进制文件导出到文件:文件开头的其他数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！