Export Blob(BINARY or VARBINARY) From SQL Table And Save It As A File

In this blog, you will learn how to export Blob(BINARY or VARBINARY) From SQL Server Table and save it as a file.

A binary value or type in SQL Server is a series of bytes (known as a byte array in some programming languages). Just like char/varchar, there are fixed-length types, binary(1-8000), and variable-length ones, varbinary(1-8000) and varbinary(max).

Sometimes we store this type of data in SQL Tables and lose the source files. This type of data is stored in a binary format which is system formatted. 

A PDF file is converted and stored in this format. And the text looks similar to this,

"0x255044462D312E340A25C3A4C3BCC3B6C39F0A322030206F626A0A3C3C2F4C656E67746820332030
20522F46696C7465722F466C6174654465636F64653E3E0A73747265616D0A789C358CBD1240401083FB
7D8AD48A75B71CABD728551EC0F8291C43E3F5ED0D26453"

Now to recreate a file from this data is not something that can be created by simple copy-paste.

Here is the process which can be used to get the source file.

DECLARE @outPutPath varchar(50) = 'C:\ExtractedFiles'
, @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), [FileName]  varchar(100), [Doc_Content] varBinary(max) )

INSERT INTO @Doctable( [FileName],[Doc_Content])
Select [RecordID],[FileUpload] FROM  [dbo].[tbl_AuthorConferenceList]

--SELECT * FROM @table

SELECT @i = COUNT(1) FROM @Doctable

WHILE @i >= 1
BEGIN

       SELECT
        @data = [Doc_Content],
        @fPath = @outPutPath +  '\' +[FileName] +'.pdf',
        @folderPath = @outPutPath
       FROM @Doctable WHERE id = @i

  --Create folder first
 
  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instance 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

Now you might end up getting compilation issues.

The reason is all the System Stored Procedures are unavailable.

To solve this we have to enable the Facets Property "OleAutomatedEnabled"