Search code examples
sql-serverbcp

SQL Server BCP format file could not be opened. Invalid name specified or access denied

My SQL Server table has an Image datatype column containing PDF's and I am trying to restore (export) them but I get stuck.

I tried different methods using BCP in SSMS but every time I export the file it is either 0 kb in size or few kb (5-10) less than the original PDF (and the file does not open). How can I restore the original pdf without being corrupted. are there any alternatives? I have 100's of files to export.

I am able to create format file using following BCP / TSQL and also export the PDF but it is corrupted (cannot open)

EXEC master..xp_cmdshell  'bcp TestDB.dbo.CLTDOCSX format nul -T -n -f D:\ImagesFromSql\formatfile.fmt'

Format File

But SQL Server throws the following errors when I run the following BCP / T-SQL

DECLARE @sql varchar(500)
SET @sql = 'BCP "SELECT DOCData FROM [TestDB].dbo.CLTDOCSX " QUERYOUT D:\ImagesFromSql\myfilename.pdf -T -f D:\ImagesFromSql\formatfile.fmt -S ' + @@SERVERNAME
EXEC master.dbo.xp_CmdShell @sql

enter image description here

I tried to run BCP command from CMD as admin but still same error. enter image description here

Solution

  • Here is how I resolved the issue.

    I found out that the data in the image column are not pdfs, they are all RAR files that can be opened using programs like 7Z or WinRAR and the actual pdfs are inside the RAR files.

    I simply replaced the .pdf with .RAR in BCP query and it worked. QUERYOUT D:\ImagesFromSql\myfilename.rar

    All the Hex values in the image column were starting with 0x504B0304 - typically represents the file signature for a ZIP archive. So, first I tried .ZIP which did not work then .RAR which worked.