Sql-server – Header is added when exporting column of type image with BCP to files

bcpsql server

I export a column of type image to files using this command:

EXEC master..xp_cmdshell 
'BCP "SELECT file_name FROM file_table WHERE id = 11 " 
queryout "C:\bcpdir\test.xml" -T -N'

In SQL Management Studio, I can see the hex value of the field starts with 0x3C3F786D6C which is correct for an XML file (<?xml).

However, the command above seem to add an 4 byte header to the file. How can I use BCP without this header?

Best Answer

The solution seem to be to first execute BCP manually, set prefix to 0 in the user dialog. And then use the generated .fmt-file as in this command:

EXEC master..xp_cmdshell 'BCP "SELECT my_column FROM my_table WHERE my_id = 11 " 
queryout "C:\bcpdir\bcpout.xml" -T -f "C:\bcpdir\bcp.fmt'

Related Solutions

Sql-server – Export SQL Server varbinary field to string using Visual Basic script in SSIS

On line 9, instead of GetType(Byte) you need GetType(Byte()).

Encoding.GetString only takes a byte array; never a single byte, so regardless of the type provided by the framework, what's there is logically not going to work.

If there is another field in the table that comes back as a Byte, you'll need to add another branch to the If statement to handle the encoding (or convert the single byte to a byte array of length 1 and use common encoding code).

SQL Server 2008 – SELECT Data Set Using Stored Procedure with BCP Error

You have CR\LF in shell invocation, a no-no. Make it a single line:

DECLARE @SQL VARCHAR(8000); 
SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull ''B1A'';" '+ 
               'queryout "F:\aaData\IPACostData\R15TData\2BSHAEOS_B1A_20121120.txt" '+
               '-f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + ''; 
EXEC xp_cmdshell @SQL;
GO

Best Answer

Related Solutions

Sql-server – Export SQL Server varbinary field to string using Visual Basic script in SSIS

SQL Server 2008 – SELECT Data Set Using Stored Procedure with BCP Error

Related Question