Accessing OLE and COM Objects from SQL Server using OLE Automation Stored Procedures

Problem

You wish to access OLE or COM objects, such as file system objects (files, folders) and their properties directly from SQL Server. This might be, for example, to load data automatically from a raw data source; to check for the existence of a directory before exporting data; or to check file properties such as date modified and size to ensure the validity of incoming data. SQL Server does not have a great deal of support for interacting with underlying objects in this way. Options do exist, for example basing your scripts in PowerShell and accessing the database as an object will allow more comprehensive use of non-database objects. Alternatively, CLRs (Common Language Runtimes) can allow you to write scripts in C# or other supported languages for assembly and execution from SQL Server. However, both of these options require some expert knowledge and preparation, when all you want is a simple procedure.

Solution

Handily, SQL Server comes supplied with ‘OLE Automation Stored Procedures’, a subset of system stored procedures which allow access to these objects. There are seven in total:

sp_OACreate
sp_OADestroy
sp_OAGetErrorInfo
sp_OAGetProperty
sp_OASetProperty
sp_OAStop

In this demonstration, I will be using sp_OACreate and sp_OAGetProperty to show how to use a FileSystemObject to check for two conditions:

The existence of a file in a predetermined directory
The last modified date of this file (so we know the file is recent)

We can return a value to SQL Server which can then be used in, for example, a notification email or to drive the execution of another procedure. This tip will also cover the other procedures in brief, and provide a guide to the syntax of these procedures.

Note that to use these extended stored procedures, you will need to use sp_configure to switch on ‘Ole Automation Procedures’:

EXEC    sp_configure 'Ole Automation Procedures', 1 
RECONFIGURE WITH OVERRIDE

Demonstration – File Size and Creation Date

Let us assume we are checking for the existence of a file called ‘OvernightInboundData_YYYYMMDD.dat’ in directory ‘C:\inbound’. The file is created there by another non-SQL Server data export process, and our job is to pick up that file and do something with it (we’re not concerned with precisely what this is here). We expect the file to be between 0.5GB and 1.0GB in size and be new, i.e. created in the last 12 hours. Let us first set up these conditions in local variables.

DECLARE    @ExpectedFolder VARCHAR(11) = 'C:\inbound\'
DECLARE    @ExpectedFilePrefix VARCHAR(29) = 'OvernightInboundData_'
DECLARE    @ExpectedAgeInHours TINYINT = 12
DECLARE    @ExpectedSizeBytesLower BIGINT = 536870912
DECLARE    @ExpectedSizeBytesUpper BIGINT = 1073741824
DECLARE    @YYYYMMDDToday CHAR(8) = CONVERT(CHAR(8),GETDATE(),112) 
DECLARE    @ExpectedFullFileName VARCHAR(255) = 
               @ExpectedFolder + @ExpectedFilePrefix + @YYYYMMDDToday + '.dat'

Now we need to take a look at how to use the syntax for these commands. To understand how this works, we ideally need to know a little about how to address COM objects. For those of you reading who are familiar with PowerShell or an older form of COM object interaction via VBScript, this might be familiar.

Let’s create a FileSystemObject object. This is a ‘container’ object which has a number of useful methods, such as CreateTextFile and OpenTextFile. By creating the ‘container’ object we can use these methods and fetch properties which interest us.

DECLARE    @returnCode INT
DECLARE    @objectToken INT
EXEC       @returnCode = 
               sp_OACreate 'Scripting.FileSystemObject', @objectToken OUTPUT 
SELECT    @returnCode

This should return 0, which indicates success. There’s a couple of important points to note here. First, we created an object of type Scripting.FileSystemObject, the handle for which is stored in output object token @objectToken, which must be declared as an INT. This INT is a reference to the object and handled by SQL Server. Secondly, we still aren’t done, we have created the object, but have yet to do something interesting with it. Don’t forget that we are using temporary variables too, so these variables are destroyed once the batch is done. We should complete our work in one execution.

Let’s now check for the existence of our file in the given directory by using our new FileSystemObject.

exec    sp_OAMethod @objectToken, 'FileExists', 
                 @returnCode OUTPUT, @ExpectedFullFileName
SELECT  @returnCode

This, in our example, returns 1 (since I have created the file for demonstration purposes). This is handy to know – we can now plug this value into another procedure, i.e. to send an e-mail affirming that data was received OK, or start another process.

Let’s see if we can retrieve some more properties and use our variables to evaluate the file and see if it’s fit for purpose.

Test 1 – Check File Size in SQL Server

Is the file size between 0.5GB and 1.0GB?

DECLARE    @FileSize BIGINT 
DECLARE    @objectTokenForFile INT 
EXEC    sp_OAMethod @objectToken, 'GetFile', 
        @objectTokenForFile OUTPUT, @ExpectedFullFileName 
EXEC    sp_OAGetProperty @objectTokenForFile, 'Size', @FileSize OUTPUT

This returns the file size in bytes to @FileSize. From here, it’s trivial to compare to our lower and upper bounds to see if this is in our expected size range.

DECLARE    @PassedTest1 BIT 
SET        @PassedTest1 = 
           CASE    WHEN @FileSize BETWEEN @ExpectedSizeBytesLower 
                  AND @ExpectedSizeBytesUpper
                   THEN 1 
                   ELSE 0
           END

Test 2 – Date and Time Value in File Name

Does the name of the file contain the date between 12 hours ago and now in YYYYMMDD format?

DECLARE    @FileName VARCHAR(255) 
EXEC       sp_OAGetProperty @objectTokenForFile, 'Name', @FileName OUTPUT
DECLARE    @PassedTest2 BIT 
SET        @PassedTest2 = 
           CASE    WHEN @FileName LIKE ('%' + @YYYYMMDDToday + '%') 
                   THEN 1 
                   WHEN @FileName LIKE ('%' + CONVERT(CHAR(8),DATEADD(HOUR,-12,GETDATE()),112) + '%') 
                   THEN 1 
                   ELSE 0
           END

Test 3 – File Date Modified Value

Does the date modified tally with this, i.e. was the last modified date in the last 12 hours also?

DECLARE    @DateModified DATETIME 
EXEC       sp_OAGetProperty @objectTokenForFile, 'DateLastModified',
              @DateModified OUTPUT
DECLARE    @PassedTest3 BIT 
SET        @PassedTest3 = 
           CASE    WHEN @DateModified 
                        BETWEEN DATEADD(HOUR, -12, GETDATE()) AND GETDATE()   
                   THEN 1 
                   ELSE 0
           END

And now we can do something with the output of these tests, like so. Uncomment the line below to return the value of the three test outputs:

-- SELECT @PassedTest1, @PassedTest2, @PassedTest3 
IF @PassedTest1 = 1 AND @PassedTest2 = 1 AND @PassedTest3 = 1 
    RAISERROR('File has passed validation tests.',10,1) 
ELSE 
    RAISERROR('File has NOT passed validation tests.', 10,1)

Of course, in place of RAISERROR you could insert a call to a stored procedure, invoke a job, or send an email.

Behind the Scenes

So what do these SP_OA* extended stored procedures actually do? Unfortunately, in SQL Server we cannot get the definition of extended stored procedures in the same way as we would the definition of stored procedures (i.e. by querying sys.sql_modules) since extended stored procedures are built from .DLL files. We can, however identify the .DLL file responsible for the extended stored procedure:

USE master
GO 
EXEC sp_helpextendedproc @funcname = 'sp_OAMethod'

In SQL Server we cannot get the definition of extended stored procedures in the same way as we would the definition of stored procedures

We can surmise the .DLL assembly accesses COM objects directly, passing through the parameters we give to SP_OA* to create/call objects or fetch properties. I’ve tried decompiling this .DLL using dotPeek and .NET Reflector for a closer look – but no luck so far. If you have any success, please post a comment below and let us know what you found!

Other Functions

In addition to SP_OACreate, SP_OAMethod and SP_OAGetProperty, we also have:

SP_OADestroy – destroys a created OLE object
SP_OAGetErrorInfo – gets error information from an OLE object (useful for robust code in conjunction with the other methods)
SP_OASetProperty – sets the property of an OLE object
SP_OAStop – stops the execution environment, meaning any open @objectToken becomes invalid and SP_OACreate will need to be invoked again

Next Steps

For a single page with all links to the OLE Automation Stored Procedures, see: http://msdn.microsoft.com/en-us/library/ms190501.aspx
Using sp_OACreate: http://msdn.microsoft.com/en-us/library/ms189763.aspx
Using sp_OAMethod: http://msdn.microsoft.com/en-us/library/ms174984.aspx
Using sp_OAGetProperty: http://msdn.microsoft.com/en-us/library/ms175079.aspx
Related article: http://www.databasejournal.com/features/mssql/article.php/1442201/Working-with-COM-Objects-from-within-T-SQL.htm

Resources

Community

Subscribe