varbinary not generating files sql

Question

Hi there,

I was using the code below code to generate the files from varbinary, it is generating the file but with 0 size

Can anyone help me where I was doing mistake? Appreciate your help

DECLARE @outPutPath varchar(100)  
, @i bigint  
, @init int  
, @data varbinary(max)   
, @fPath varchar(max)    
, @folderPath  varchar(max)  
,@sqlcommand varchar(max)  
,@targetLocation varchar(max)  
  
--Get Data into temp Table variable so that we can iterate over it   
DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num]  varchar(max) , [FileName]  varchar(max), [Doc_Content] varBinary(max) )  
  
  
/****** Script for SelectTopNRows command from SSMS  ******/  
SELECT  
      @sqlcommand=[SourceQuery], @outPutPath=[TargetLocation]  
  FROM [dbo].[FilesMetadata]  
  where Status ='Y'  
  
INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content])  
exec(@sqlCommand)  
  
  
  
SELECT @i = COUNT(1) FROM @Doctable     
  
WHILE @i >= 1     
  
  
  
BEGIN      
  
SELECT   
    @data = [Doc_Content],  
    @fPath = @outPutPath + '\' + [FileName],  
    @folderPath = @outPutPath --+ '\'+ [Doc_Num]  
FROM @Doctable WHERE id = @i  
  
--Create folder first  
  
--EXEC  [dbo].[CreateFolder]  @folderPath  
  
  
EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created  
--EXEC sp_OASetProperFolder @init, 'Folderpe', 1;    
EXEC sp_OAMethod @init, 'Open'; -- Calling a method  
EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method  
EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method  
EXEC sp_OAMethod @init, 'Close'; -- Calling a method  
EXEC sp_OADestroy @init; -- Closed the resources  
print 'Document Generated at - '+  @fPath     
  
--Reset the variables for next use  
SELECT @data = NULL    
, @init = NULL  
, @fPath = NULL    
, @folderPath = NULL  
SET @i -= 1  
END     

Answer 1

Try to do your code the same way as in this thread
https://stackoverflow.com/questions/4056050/script-to-save-varbinary-data-to-disk/40520791
although I don't know if it helps, the method seems really weird to me. In the answer in this thread one line is missing in your code:

EXEC sp_OASetProperty @Init, 'Type', 1 -- before the rest of your code

Answer 2

Hi @srk
Maybe try modify @fPath = @outPutPath + '\' + [FileName], to @fPath = @outPutPath + '\' + [FileName] +'.pdf'

Besides, how about using BCPcommond, like this sample:

DECLARE @FileName varchar(50)  
DECLARE @bcpCommand varchar(2000)  
DECLARE @Code varchar(50)  
DECLARE @RowID int  
  
DECLARE Image_cursor CURSOR FOR  
SELECT RowID, Code FROM Table1  
  
OPEN Image_cursor  
FETCH NEXT FROM Image_cursor INTO @RowID, @Code  
WHILE @@FETCH_STATUS = 0  
BEGIN  
  SET @FileName = 'C:\Test\' + @Code + '.jpg'  
  SET @bcpCommand = 'bcp "SELECT ImageField From Test.dbo.Table1 Where RowID = ' + str(@RowID) + '" queryout "'  
  SET @bcpCommand = @bcpCommand + @FileName + '" -T -c'  
  EXEC xp_cmdshell @bcpCommand  
-- Get the next Code.  
  FETCH NEXT FROM Image_cursor INTO @RowID, @Code  
END  
  
CLOSE Image_cursor  
DEALLOCATE Image_cursor  

Refer to this similar thread for more details: Dumping varbinary(max) column values to files on harddisk using a SQL script

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Many thanks for your information .. It is working now