File Cabinets - redjar

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.

The following beta exams are out for .NET Framework 3.5

• 71-564: Pro: Designing and Developing ASP.NET Applications Using the Microsoft® .NET Framework 3.5 (expires 12/01/2008, code RSC43)
• 71-565: Pro: Designing and Developing Enterprise Applications Using the Microsoft® .NET Framework 3.5 (expires 12/03/2008, code 999TR)
• 71-505: TS: Microsoft .NET Framework 3.5, Windows Forms Application Development (expires 12/15/2008, code X356Y)

The exams are free and the exam will be credited towards your certifications if you pass.  The upside is taking the test and seeing your skills.  The downside is… it takes some of your time.  Kind of a weak downside but that’s the best I can come up with.

Found this the other day when just browsing around:

Welcome to the Microsoft® ASP.NET 2.0 Webcast Series, sponsored by Microsoft, Dr. Dobb’s, and O’Reilly. Choose from 45 webcasts organized by track—JSP, PHP, and ColdFusion. Each track has been designed to take advantage of your existing Web development skills and includes insightful content contributed by Dr. Dobb’s and O’Reilly. Select the track you’re most familiar with, and then pick the webcast topics that’ll make your skill set even more versatile.

When you attend three webcasts, you’ll receive an ASP.NET 2.0 Development Pack, including Microsoft Visual Studio® 2005 Professional Edition 90-day DVD trial, five chapters of Programming ASP.NET 2.0 Core Reference, by Dino Esposito, and more.

I’ve got this thing about getting FREE stuff… I love to get it.  And this is a decent opportunity to learn some stuff… wow… talk about win/win situations.
Actually I don’t know a thing about JSP, PHP, or ColdFusion… but if it’s not Microsoft it should be and I’ll do my part to add more minions to the fold.

A very good article by Jeff Smith over at SQL Team on Using Parameters when using dynamic SQL from .NET applications.  While I am in the always use stored procedures camp, not sure if I am a resident preacher there but I am at least in the choir, the article covers the bases on Best Practices for using dynamic SQL .