Why xp_cmdshell exports empty PDF files from database that stored in image datatype

Question

I have query that extracts PDF files into directory. But for some reason all files are empty (0 KB). Column PDF stored as image datatype. I know its obsolete but I am not the owner of the database. Would you suggest any workaround?

CREATE TABLE #tblPolicyForms
(
    FormID INT NOT NULL
    ,PDF varbinary(max) NULL
    ,PDF_FIlename VARCHAR(max)
)


INSERT INTO #tblPolicyForms
SELECT  FormID,
        PDF,
        PDF_FileName
FROM    [dbo].[tblPolicyForms]
WHERE   FormID IN (19,20,21,22)
--select * from #tblPolicyForms

DECLARE @FormID varchar(4);
DECLARE @FileName VARCHAR(200);

DECLARE FormID_cursor CURSOR FOR 
SELECT
    FormID
    ,PDF_FIlename
FROM
  #tblPolicyForms
WHERE FormID IN (19,20,21,22)



OPEN FormID_cursor

FETCH NEXT FROM FormID_cursor
INTO @FormID, @FileName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @cmd VARCHAR(8000) = 'BCP "SELECT PDF FROM #tblPolicyForms " queryout "C:\Users\oserdyuk\Documents\ExportDir\'+@FileName+'" -T -N'
    --print @cmd
    EXEC master.dbo.xp_cmdshell @cmd;

FETCH NEXT FROM FormID_cursor
INTO @FormID, @FileName

END

CLOSE FormID_cursor
DEALLOCATE FormID_cursor

kirchner · Answer 1 · 2017-11-09T18:13:41.213

The thing is BCP is opening another connection to SQL Server, and regular temp tables like your #tblPolicyForms are private to the connection. The BCP is probably generating an error which you don't get to see.

Can you change your temp table to a global one? This way the other connection (BCP) will be able to access it. You need to use two pound signs like this:

CREATE TABLE ##tblPolicyForms
(
    FormID INT NOT NULL
    ,PDF varbinary(max) NULL
    ,PDF_FIlename VARCHAR(max)
)

You also need to change every reference from #tblPolicyForms to ##tblPolicyForms (single to double pounds). Be aware that if the procedure is executed in parallel by different sessions you will receive an error indicating that ##tblPolicyForms already exists.

Another thing, you are exporting every row into every file, so every file ends up the same size as you have seen. You need a WHERE clause in your BCP like this:

DECLARE @cmd VARCHAR(8000) = 'BCP "SELECT PDF FROM ##tblPolicyForms WHERE FormID = ''' + @FormID + '''  " queryout "C:\Users\oserdyuk\Documents\ExportDir\'+@FileName+'" -T -N'

But even then things might not work properly because bcp will prepend a 8 byte header to your row. Since bcp is an export/import tool it will create a file it can later import back into your database.

Thanks. The size of each file now is 63 KB which is weird that they are all the same size. But when I try to open it - the Acrobat Reader gives me an error: " could not open because its either not a supported file type or because file has been damaged" — Serdia, Nov 08 '17 at 22:26
I didn't realize, but you are exporting every row to every file. You need a WHERE clause in your BCP. I'll edit the answer above with that information. — kirchner, Nov 09 '17 at 18:09

Why xp_cmdshell exports empty PDF files from database that stored in image datatype

1 Answers1