This is gonna be a quick post, but I wanted to throw out a couple easy ways to avoid using xp_cmdshell.

Unfortunately this xp has gotten a bad rep, but only because it’s been abused.  If you use it properly there’s no reason why you can’t continue to get some good use out of it for many years to come.  Actually, I use it for my backup routines for a couple reasons… first, when I wrote them there really wasn’t a better choice.  And second, while I can architect a solution around it quite easily, it’s ridiculous to go so far out of my way to code around something that isn’t a huge problem.  The solution would be more complicated than the original problem, so why bother?  Oh yeah, and since I’m in a mixed shop there’s also that problem with having something like powershell on every box and I don’t wanna have to have a different routine for my SQL2K boxes, than I do for all the rest.  So using xp_cmdshell is still a good fit for me. 

Anyay though, there are some good reasons to avoid it when it’s not necessary (part of using it responsibly).  So we’re going to talk about 2 XPs today that will help you do just that.

First, there’s xp_FixedDrives.  This will report all the drives on your server and the amount of free space in MB.  Now, this can’t replace a well-formed PS script that can give you a lot more info, but it’s a nice start when you just need to look at drive space real quick.

Second, we have xp_create_subdir.  This handy little xp will let you create a directory.  You just pass it the path you want to create and you’re golden.  So it would look something like this:

xp_create_subdir ‘C:\Mydir\Anotherdir\theFinalDir’

And just like in DOS it’ll build the whole path for you so none of those dirs have to exist ahead of time.

Anyway, hope this finds someone well.