Search code examples
sql-serverblobbcp

SQL Server BCP export binary to file: extra data at the begining of the file

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

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

Solution

  • 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.

    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.

    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.