Search code examples
sql-serverbcp

bcp export varbinary to flat file

I want to export varbinary with bcp to a flat file (csv). It seems to work, but in front of the 0x is a strange enconding, I can not get rid of. Can someone explain what I'm doing wrong? Following the bcp-command I execute and the screenshot from notepad++

PS C:\Windows\system32> bcp "SELECT TOP 5 CONVERT(varchar(max), [Bild],1) AS SomeImageFieldAsHex FROM [SWTA_HH].[dbo].[Fenster]" queryout "c:\export.csv" -d "SWTA_HH" -T -n -r \n

varbinary encoding

Thanks Darius

Solution

  • As per the bcp Utility documentation, the -n switch is instructing BCP to output data in "native" format, which includes binary information to allow a receiving SQL Server to ingest the data again using the correct data types.

    If you're wanting to output these varbinary values to a .CSV (comma-separated values) file then you'll probably want to use the -c switch instead, for character data, e.g.:

    bcp "SELECT TOP 5 CONVERT(varchar(max), [Bild], 1) AS SomeImageFieldAsHex FROM [SWTA_HH].[dbo].[Fenster]" queryout "c:\export.csv" -d "SWTA_HH" -T -c -r \n