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

txu3uszq  于 2023-04-19  发布在  SQL Server
关注(0)|答案(2)|浏览(134)

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'

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

I tried to run BCP command from CMD as admin but still same error.

vmdwslir

vmdwslir1#

Here's a complete test script you can use:

if object_id('CLTDOCSXTest') IS NOT NULL
    drop table CLTDOCSXTest 
create table CLTDOCSXTest (DOCData Image)

-- Put file
insert into CLTDOCSXTest
SELECT * FROM OPENROWSET(
   BULK 'C:\SQLServer\MSSQL11.SIGGE\MSSQL\DATA\sample.pdf',
   SINGLE_BLOB) AS DATA;

-- Create format file
EXEC master..xp_cmdshell  'bcp TestDB.dbo.CLTDOCSXTest format nul -T -n -f C:\SQLServer\MSSQL11.SIGGE\MSSQL\DATA\formatfile.fmt'

-- Export file to pdf
DECLARE @sql varchar(500)
SET @sql = 'BCP "SELECT DOCData FROM [TestDB].dbo.CLTDOCSXTest " QUERYOUT C:\SQLServer\MSSQL11.SIGGE\MSSQL\DATA\output.pdf -n -T -f C:\SQLServer\MSSQL11.SIGGE\MSSQL\DATA\formatfile.fmt -S ' + @@SERVERNAME
EXEC master.sys.xp_CmdShell @sql

-- Compare files, should return 1
SELECT COUNT(*)
FROM CLTDOCSXTest c
INNER JOIN OPENROWSET(
   BULK 'C:\SQLServer\MSSQL11.SIGGE\MSSQL\DATA\output.pdf',
   FORMATFILE ='C:\SQLServer\MSSQL11.SIGGE\MSSQL\DATA\formatfile.fmt') data
    ON   CAST(c.DOCData AS VARBINARY(MAX)) = CAST(data.docdata AS VARBINARY(MAX));

It creates a table like yours, imports a pdf, creates a bcp format file, exports the pdf and compares it to the imported one. I have also tested with really large binary files and they work too.

Note that you need to remove the format files since i don't think they're being recreated

4szc88ey

4szc88ey2#

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.

相关问题