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;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

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
)
GO
INSERT INTO ExtractFile
SELECT bulkcolumn, 'sqlindia-sample', '.jpg' FROM OPENROWSET(BULK 'E:\others\sqlindia-sample.jpg', SINGLE_BLOB) t
UNION ALL
SELECT bulkcolumn,'steve-jobs', '.jpg' FROM OPENROWSET(BULK 'E:\others\steve-jobs.jpg', SINGLE_BLOB) t
UNION ALL
SELECT bulkcolumn,'Database Mirroring_Configure_Steps', '.doc' FROM OPENROWSET(BULK 'E:\others\Database Mirroring_Configure_Steps.doc', SINGLE_BLOB) t
UNION ALL
SELECT bulkcolumn,'example', '.pdf' FROM OPENROWSET(BULK 'E:\others\example.pdf', SINGLE_BLOB) t

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.


/*
WWW.SQLINDIA.COM
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))

INSERT INTO @table
SELECT file_data, [file_name], file_extension FROM ExtractFile

SELECT @i = COUNT(1) FROM @table

WHILE @i >= 1
BEGIN

    SELECT
    @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
SELECT @data = NULL
, @fname = NULL
, @xtn = NULL
, @init = NULL
, @fPath = NULL

SET @i -= 1
END

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.

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 : *
8 + 22 =