How To Read File Attributes using OLE Object with SQL Server

• Download script - 1.48 KB

Introduction 

This article will demonstrate how we can read file attributes using OLE Object with SQL Server. Here are a number of T-SQL Stored Procedures, based on the use of the FileSystem Object (FSO) that may just get you out of a tight corner.

Background 

It is not necessary that developers must use a .NET class library for getting file attributes information when the application is completely based on DBMS. It is always better to use the standard techniques provided by SQL Server where possible. SQL Server provides several "standard" techniques for file manipulation based on the use of the FileSystem Object (FSO).

Using the Code

Thankfully, when armed with OLE Automation and the FileSystem Object (FSO), all sorts of things are possible. The FileSystem Object was introduced into Windows to provide a single common file-system COM interface for scripting languages. It provides a number of handy services that can be accessed from T-SQL. I'll provide a few details on the FSO along the way, but let's start with an example of some of these procedures in action. You'll need to enable OLE automation on your test server in order to follow along.

I wrote a Stored Procedure dbo.spGetFileAttributes to read file attributes using the FileSystem Object (FSO). A code example is given below:

/****** Object:  StoredProcedure [dbo].[spGetFileAttributes ]    
Script Date: 06/17/2009 20:27:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Md. Marufuzzaman>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================

/*
EXEC dbo.spGetFileAttributes  'c:\autoexec.bat'
*/

CREATE PROCEDURE [dbo].[spGetFileAttributes ]
 @FILE_PATH VARCHAR(MAX)
AS
BEGIN
DECLARE @intResult        INT         --the HRESULT returned from 
       ,@objFileSystem    INT      --the FileSystem object
       ,@objFile        INT            --the File object
       ,@ErrorObject    INT        --the error object
       ,@ErrorMessage    VARCHAR(255)--the potential error message
       ,@Path            VARCHAR(100)
       ,@shortPath        VARCHAR(100)
       ,@Type            VARCHAR(100)
       ,@Created        DATETIME
       ,@Accessed        DATETIME
       ,@Modified        DATETIME
       ,@Attributes        INT
       ,@size            INT

SET NOCOUNT ON

 SELECT @intResult=0,@errorMessage='Creating the file system object '
 EXEC @intResult = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUTPUT
 
 IF @intResult=0 SELECT @errorMessage='Accessing the file ''' + _
	@FILE_PATH + '''', @ErrorObject=@objFileSystem
 IF @intResult=0 EXEC @intResult = sp_OAMethod @objFileSystem, _
	'GetFile',  @objFile OUTPUT,@FILE_PATH

 IF @intResult=0 SELECT @errorMessage=_
	'Getting the attributes of ''' + @FILE_PATH + '''', @ErrorObject=@objFile
 IF @intResult=0 EXEC @intResult = sp_OAGetProperty  @objFile, 'Path', @path OUTPUT
 IF @intResult=0 EXEC @intResult = _
	sp_OAGetProperty  @objFile, 'ShortPath', @shortPath OUTPUT
 IF @intResult=0 EXEC @intResult = sp_OAGetProperty  @objFile, 'Type', @Type OUTPUT
 IF @intResult=0 EXEC @intResult = _
	sp_OAGetProperty  @objFile, 'DateCreated', @Created OUTPUT
 IF @intResult=0 EXEC @intResult = _
	sp_OAGetProperty  @objFile, 'DateLastAccessed', @Accessed OUTPUT
 IF @intResult=0 EXEC @intResult = _
	sp_OAGetProperty  @objFile, 'DateLastModified', @Modified OUTPUT
 IF @intResult=0 EXEC @intResult = _
	sp_OAGetProperty  @objFile, 'Attributes', @Attributes OUTPUT
 IF @intResult=0 EXEC @intResult = _
	sp_OAGetProperty  @objFile, 'Size', @size OUTPUT

IF @intResult<>0
       BEGIN
           DECLARE  @Source            VARCHAR(255)
                   ,@Description    VARCHAR(255)
                   ,@Helpfile        VARCHAR(255)
                   ,@HelpID            INT
           
           EXECUTE sp_OAGetErrorInfo  @errorObject, 
                   @source OUTPUT,@Description OUTPUT,
                                   @Helpfile OUTPUT,@HelpID OUTPUT

           SELECT @ErrorMessage='Error whilst '
                                   + @Errormessage+', '
                                   + @Description
           RAISERROR (@ErrorMessage,16,1)
       END
       
 -- Release the reference to the COM object */
 EXEC sp_OADestroy @objFileSystem
 EXEC sp_OADestroy @objFile
 
 SELECT  [Path] = @Path
        ,[ShortPath] = @shortPath
        ,[Type] = @Type
        ,[Created] = @Created 
        ,[Accessed] = @Accessed
        ,[Modified] = @Modified
        ,[Attributes] = @Attributes
        ,[Size] = @size       

END

GO 

OLE Automation

Each OLE Automation Stored Procedure returns an integer code that is the HRESULT returned by the underlying OLE Automation operation. When an error happens, the HRESULT returns an error code rather than the 0 which signals success, which then has to be turned into a meaningful error message with the sp_OAGetErrorInfo procedure.

The full set of OLE automation procedures are given below:

Stored Procedure	Description
sp_OACreate	Creates an instance of the OLE object on an instance of Microsoft SQL Server
sp_OADestroy	Destroys a created OLE object
sp_OAGetErrorInfo	Obtains OLE Automation error information
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

More on the FSO and OLE Automation

There are all sorts of things you can do with the FSO. You can copy files, move files, create folders, delete files, get the names of special directories, and so on. This may sound esoteric, but sometimes the simplest backup procedures require such operations.

The Filesystem Object, on which all the stored procedures in this article rely, is a component of the scripting runtime library. As it is a COM object, it is readily accessible from the set or stored procedures built-in to SQL Server called the OLE Automation Stored Procedures. These allow a connection, through T-SQL commands, to create and use COM-based objects.

Enable OLE Automation

Use the OLE Automation Procedures option to specify whether OLE Automation objects can be instantiated within Transact-SQL batches. This option can also be configured using Policy-Based Management or the sp_configure Stored Procedure.

The OLE Automation Procedures option can be set to the following values.

Value	Description
0	OLE Automation Procedures are disabled. Default for new instances of SQL Server.
1	OLE Automation Procedures are enabled.

When OLE Automation Procedures are enabled, a call to sp_OACreate will start the OLE shared execution environment.

The current value of the OLE Automation Procedures option can be viewed and changed by using the sp_configure system Stored Procedure.

The following example shows how to view the current setting of OLE Automation Procedures.

EXEC sp_configure 'Ole Automation Procedures';
GO 

The following example shows how to enable OLE Automation Procedures:

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

Reference: MSDN

Conclusion

I hope that this article will be helpful to you. Enjoy!