Export Blob(BINARY or VARBINARY) From SQL Server Table and save it as a file

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).

Some time we store this type of  data in SQL Tables and loose the source files. This type of data 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,

"0x255044462D312E340A25C3A4C3BCC3B6C39F0A322030206F626A0A3C3C2F4C656E6774682033203020522F46696C7465722F466C6174654465636F64653E3E0A73747265616D0A789C358CBD1240401083FB7D8AD48A75B71CABD728551EC0F8291C43E3F5ED0D26453"

Now to recreate a file from this data is not something which can be crated 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"