Extracting BLOB data to files

Trying to get a file out of a BLOB field and came up with this script to do it.

--Execute this once before running the following script.
--sp_configure 'show advanced options', 1;
--GO
--RECONFIGURE;

--GO
--sp_configure 'Ole Automation Procedures', 1;
--GO

--RECONFIGURE;
--GO

declare @init int

declare @file varbinary(max) = CONVERT(varbinary(max),(select FILE_CONTENTS_TX from DCOPS_January.DCOPS_OWNER.TESA_DB_DOCUMENTATION WHERE reference_ID = 44545
AND DOCUMENTATION_ID=413228)
)



DECLARE @FileName AS VARCHAR(50)


select @FileName=SOURCE_NAME_TX from DCOPS_January.DCOPS_OWNER.TESA_DB_DOCUMENTATION WHERE reference_ID = 44545
AND DOCUMENTATION_ID=413228


declare @filepath nvarchar(4000) = N'c:\temp\' + @FileName


SELECT @filepath


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, @file; -- Calling a method
EXEC sp_OAMethod @init, 'SaveToFile', NULL, @filepath, 2; -- Calling a method
EXEC sp_OAMethod @init, 'Close'; -- Calling a method
EXEC sp_OADestroy @init; -- Closed the resources


Source:




https://stackoverflow.com/questions/10325338/fastest-way-to-export-blobs-from-table-into-individual-files