By: Derek Colley | Updated: 2014-08-21 | Comments (1) | Scripts
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.
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:
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'
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!
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
- 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
Last Updated: 2014-08-21
About the author
View all my tips