Issue with BCP Exported File and How to Fix It.Posted: March 24, 2016
Using bcp to export binary data from column varbinary(max) to file system is easy enough.
But when you try to open these files, you may run into problems.
The problem is that bcp added 8 bytes to the exported file when you use -n flag.
To change this behavior, you need to use format file to rescue.
Create a dummy table with one varbinary(max) column and use this table to create a format file.
You can use either fmt or xml format as your format file.
Modify this generated format file and change the 8 in the third column to 0 and save it.
The final step is to use this format file for your bcp export.
if object_id('mytemp1','U') is not null drop table mytemp1 create table mytemp1 ( ABinaryCol varbinary(max)) DECLARE @cmd VARCHAR(4000) set @cmd = 'BCP [mydb1].[dbo].[mytemp1] format nul -f "C:\temp\myFormatFile1.xml" -n -T -S'+ @@servername exec master..xp_cmdshell @cmd
1 SQLBINARY 8 0 “” 1 ABinaryCol “”
1 SQLBINARY 0 0 “” 1 ABinaryCol “”
The final bcp:
declare @name varchar(300)='myfile.doc' declare @Command VARCHAR(4000) SET @Command = 'bcp "select datacol from db.[dbo].[datatable] where id =1 queryout "c:\temp\' + @name + '" -f "C:\temp\myFormatFile1.xml" -T -S '+ @@servername ; -- PRINT @Command EXEC xp_cmdshell @Command,NO_OUTPUT