Forum Discussion
Digsley
Nov 24, 2022Copper Contributor
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