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.
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: