SQL Server has never been short of ways to read from and write to files and it is always better to use the standard techniques provided by SQL Server where possible.However, most of them are really designed for reading and writing tabular data and aren’t always trouble-free when used with large strings or relatively unstructured data.It is also useful where the data feed has to be validated before one can parse it into the final SQL data format.
It provides everything that is necessary for file or folder operations.It also allows for most simple file reading and writing operations, and to gather information about drives, directories and files.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_OAGet Error Info procedure.With such a function, one can then do one’s own parsing and checking of data. Create the uft Read File As Table stored procedure in your test database, and try it out with something like: Just fill in an existing file name and path to the file you wish to read, instead of ‘My Path‘ and ‘My File Name‘, and away you go.
This is a method I use for reading web logs and gathering usage statistics.
It provides a number of handy services that can be accessed from TSQL.
In this article, I provide examples of stored procedures that use this interface to allow you to: I’ll provide a few details on the FSO along the way, but let’s start with examples of some of these procedures in action.
This may sound esoteric, but sometimes the simplest backup procedures require such operations.
I pause here just to give some minimum necessary background details on FSO, and on OLE Automation.
(Removable-media drives need not have media inserted for them to appear in this collection).