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

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.

OLE_AUTOMATION_01

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.

OLE_AUTOMATION_02

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.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: