FileSystemHelper SQL Server CLR – Codeplex

A while back I decided I need to get down to brass tacks and figure out SQL Server CLR.  I’d looked at examples and seen some pretty cool stuff being done with it.  Unfortuantely, I hadn’t taken the time to become acquianted enough to know enough about the unknown.  This limited my ability to be able to adequately recommend or not recommend projects that might utilize CLR.

Simlarily, along those lines, as a DBA I need to understand the impact of different CLR assemblies and what the effect of implementing A verus B will be.  Verifying that it is being deployed securely is as important as what it is doing to the server.  Along with this, functionality like xp_cmdshell opens huges holes in SQL Server instances that CLR can plug nicely with its file system access.

Finally, I wasn’t about to drop a few hundred dollars per SQL Server instance for someone else’s third party tool that I could easily write myself.  If you try to convince a client to put that kind of money into such a simple tool. 

Now that you have the trifecta of reasons why I worked on this, lets talk about the project: 

FileSystemHelper SQL Server CLR

 FileSystemHelper SQL Server CLR provides a collection of CLR stored procedures and functions for interacting with the file system. Using these stored procedures and functions will allow you to avoid enabling xp_cmdshell on your SQL Server instances.

The project creates the following functions and stored procedures:

• Utility.DirectoryList Table valued user-defined function that accepts @Path and @Extension and returns information on the directory. Leaving the @Extension variable blank will return all files in the directory. The table returned provides Name, Directory, Size, DateCreated, DateModified, and Extension.
• Utility.DirectoryCreate Stored procedure that creates a directory based on the @Path value provided.
• Utility.DirectoryDeleteContents Stored procedure that deletes the contents of a directory based on the @Path value provided.
• Utility.DirectoryDelete Stored procedure that deletes a directory based on the @Path value provided.

 Below are the references I recall using as I put this together.  They were a trememdous help and I’m not sure if I could have figured some of this out without that information.  There are probably a few more I am not recalling off the top of my head, but wanted to include those I reference most often or spent significant time looking at.

• Trading in xp_cmdshell for SQLCLR (Part 1) – List Directory Contents
• Signing an Assembly with a Certificate (Visual Studio)
• Exception handling in SQLCLR

The project is located here if you didn’t notice from above.  If you’d like to contribute feel free to volunteer, I’m mostly interested in having a collection of tools available that can remove the need for xp_cmdshell.

Known Issues

There is still an issue with the signing of the certificate that force me to recompile the DLL for each new maching I take it to.  I’m certain why this happens and am actively trying to figure that part out.  When I do, I’ll get the deployment fixed and post a summary on what I did for that.