XML FeedsToday’s tip is a short but powerful one. On many occasions a DBA is forced to deal with files in some way. It may be the need to download and move a file (or folder) around in order to prepare for importing the contents into a database or simply maintain the contents or auditing levels of them.
Working with files through SQL Server is more difficult than your everyday select of data from a database. SQL Server is made to stay within the walls it has built. This means we have to use other methods in order to accomplish what we need to do with file and folders.
Jason Strate (Blog | Twitter) had an idea of putting together one method that I have also used over the years – SQLCLR File System Helper. SQLCLR provides a path for a DBA and Developer to external resources that once was extremely hard to access from within a database. With SQLCLR we’ve been given the power of writing .NET code and placing and executing it directly within our databases. Now this can be dangerous given the nature of the resource utilization when it comes to .NET. However, done with planning and thought beforehand, using SQLCLR can be extremely valuable.
Jason and I have put together a set of procedures and functions that are most common in respect to file and folder operations.
Utility.DirectoryCreate
Utility.DirectoryDelete
Utility.DirectoryDeleteContents
Utility.DirectoryList
Utility.DirectoryMoveTo
Utility.DirectorySize
Utility.FileCopy
Utility.FileCreate
Utility.FileDelete
Utility.FileMove
Utility.FileRename
Utility.FileSearch
Utility.FileSearchInternal
The set of procedures and functions that are available cover a wide range of needs when you are working with files and folders. Some of these procedures are used daily on my own scheduled tasks and very beneficial in making those schedules tasks run smooth and clean.
Take a look at your own SQL Server tasks that are running either on a schedule or manually and see how these methods can help you in making them more efficient.
As always with Codeplex items, leave us feedback on the project so Jason and I can continue on making it better and more robust.
I find the concept intriguing. However, I'd be concerned about the security implications of this. You open up more possibilities for damage if someone finds an injection path to your db. 
Thank you so very much!!! I was completely lost and now I found your article and my Brain is happy again :)
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.