Binary data into filesystem using OLE automation in SQL Server

Recently I have been assigned to do some file system operation from SQL Server like saving a physical file into database in binary format and extract back the same file into file system when needed. This could have been done using cmd, powershell, vbs etc. but the whole business logic was inside the SQL server so I did it through OLE automation to achieve the same.source-code

Before moving ahead to the examples directly. Lets take a look at the objectives first.

What is OLE Automation?
OLE Automation is an inter-process communication mechanism created by Microsoft. It is based on a subset of Component Object Model (COM) that was intended for use by scripting languages – originally Visual Basic – but now are used by several languages on Windows and with SQL Server too.

How to activate OLE Automation objects in SQL Server?
By default it comes in disabled mode in SQL Server, we have to enable it manually before using the same

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

OLE Automation Procedures:

sp_OACreate Creates an instance of the OLE object on an instance of Microsoft SQL Server
sp_OAGetProperty Gets a property value of an OLE object
sp_OASetProperty Sets a property of an OLE object to a new value
sp_OAMethod Calls a method of an OLE object
sp_OAStop Stops the server-wide OLE Automation stored procedure execution environment
sp_OAGetErrorInfo Obtains OLE Automation error information
sp_OADestroy Destroys a created OLE object

Byte Array into the table from file system:

Let’s put some files into the database table in binary format.

CREATE TABLE [dbo].[ExtractFile](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[file_data] [varbinary](max) NULL,
	[file_name] [varchar](100) NULL,
	[file_extension] [varchar](10) NULL
SELECT bulkcolumn, 'sqlindia-sample', '.jpg' FROM OPENROWSET(BULK 'E:\others\sqlindia-sample.jpg', SINGLE_BLOB) t
SELECT bulkcolumn,'steve-jobs', '.jpg' FROM OPENROWSET(BULK 'E:\others\steve-jobs.jpg', SINGLE_BLOB) t
SELECT bulkcolumn,'Database Mirroring_Configure_Steps', '.doc' FROM OPENROWSET(BULK 'E:\others\Database Mirroring_Configure_Steps.doc', SINGLE_BLOB) t
SELECT bulkcolumn,'example', '.pdf' FROM OPENROWSET(BULK 'E:\others\example.pdf', SINGLE_BLOB) t


Byte Array out from the table to file system:

Now we have successfully uploaded couple of byte arrays into database table. Let’s have it extracted back into file system.

Date: 10-01-2014 (MM:DD:YY)
DECLARE @outPutPath varchar(50) = 'D:\temp'
, @i int
, @init int
, @data varbinary(max)
, @fname varchar(100)
, @fPath varchar(100)
, @xtn varchar(10)

DECLARE @table TABLE (id int identity(1,1), file_data varbinary(max), file_n varchar(100), file_x varchar(10))

SELECT file_data, [file_name], file_extension FROM ExtractFile

SELECT @i = COUNT(1) FROM @table

WHILE @i >= 1

    @data = CONVERT(VARBINARY(MAX), file_data, 1)
    , @fname = file_n
    , @xtn = file_x
    , @fPath = @outPutPath + '\' + file_n + file_x
    FROM @table WHERE id = @i

  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
  EXEC sp_OASetProperty @init, 'Type', 1; -- Set property value to the instance
  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 insatnce

--Reset the variables for next use
, @fname = NULL
, @xtn = NULL
, @init = NULL
, @fPath = NULL

SET @i -= 1


As we can see the four files from “ExtractFile” table have been successfully extracted to the given path.

That’s all folks for today! I will discuss more about OLE automation and its usage in upcoming posts.

One thought on “Binary data into filesystem using OLE automation in SQL Server”

  1. Hello,
    I’ve tried this method both xls and xlsx type of files but none of them was successful. The file creates on D disk but excel can not open the file. It gives “type and extension is not same” like error. What should i do?
    Thanks for your help

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve : *
30 × 6 =