Sql Server Blob Image Column - Extracting With Bcp Queryout - Corrupted Files And Bug

I need to export pdf and image files from a column in SQL Server 2012. Jonas's 3-step process in 'How to export an image column to files in sql server' is the clearest instruction

Solution 1:

You are using parameter -f "C:\bcpdir\bcpfile.fmt" but from my experience that should be -fC "C:\bcpdir\bcpfile.fmt". To be honest I don't remember anymore why... I once made something similar to export files (.zip) from database and my command has -fC parameter for the export file. I whish I could give you a proper explanation. Anyway, HTH.

Try the following command:

EXEC master..xp_cmdshell 'BCP"SELECT data FROM CentricityPM.dbo.TempImageFour" QUERYOUT "C:\exportdir\testfile.pdf" -T -fC "C:\bcpdir\bcpfile.fmt"'

An alternative is to specify the -C RAW option. This specifies that no conversion is done from one code page to another.

EXEC master..xp_cmdshell 'BCP"SELECT data FROM CentricityPM.dbo.TempImageFour" QUERYOUT "C:\exportdir\testfile.pdf" -T -f "C:\bcpdir\bcpfile.fmt" -C RAW'

Also, make sure that your format file has SQLBINARY as data type for your column.