Forum Discussion

Digsley's avatar
Digsley
Copper Contributor
Nov 24, 2022

TSQL export blob files failing...

The code is running without error and showing files being output to the location but they are not there. The permissions look good so I'm curious if anyone may know what I'm missing. Thanks much

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO


DECLARE @outPutPath varchar(50) = 'E:\tmp'
, @i bigint
, @init int
, @data varbinary(max)
, @fPath varchar(max) 
, @folderPath  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(100) , [FileName]  varchar(100), [Doc_Content] varBinary(max) )
 
INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content])
Select [DOCNO],CONCAT([DOCNO],'.',[EDITOREXTN]) as FileName ,[LIVE_OBJ] FROM  [dbo].[DOCLIVE]
 
--SELECT * FROM @table
 
SELECT @i = COUNT(1) FROM @Doctable
 
WHILE @i >= 1
BEGIN
 
       SELECT
        @data = [Doc_Content],
       @fPath = @outPutPath + '\JSRFiles\' +[FileName]
       FROM @Doctable WHERE id = @i
 
  --Create folder first

 
  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
  EXEC sp_OASetProperty @init, 'Type', 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
 



 

 

 

 

No RepliesBe the first to reply

Resources