SUBST, friend or foe

SUBST, for those that don't know is a command in Windows that allows one to SUBSTitute one drive letter for some path. For example, if I liked K better for my C drive, I could Start->Run and then type in SUBST K: C:\ and whammo, I now have a K drive that shows up in windows explorer and I can do things like create a file on K:\Windows and it'll show up on both the C drive as well as the K drive. Big whoop, right? It can be handy in situations where our servers expect data to be on the D drive and the local developers only have a C. Data locations are driven by configuration files so it's not bad except when you do a fresh install and forget to update that file. Trivial but hey, this is just a trick, not rocket surgery or life altering.

However, not everything plays well with it. The first thing I noticed was that when I attach a thumb drive to my machine, it will take the next available drive. SUBST doesn't _really_ mark that drive letter as taken so if K was your next available drive and you've mapped to it, connect a thumb drive which also claims K and then try to access K. Windows will get all sorts of confused as to what to do with it. At this point, you need to remove the substituted drive and K will be available for your removable media. To knock down a substitution, the command would be SUBST /D K:

The next interesting thing is defrag. Disk defragment, well if you're reading this I'm going to assume you not only know where it it and what it does. So, right click on your K drive and it'll say Local Disk (K:) Properties and you can defrag but wait, what's this defrag is smart enough to know that we're really only talking about that hard drive there and it's called C:

The last item I'll comment on is the one that actually drove this post. We send email through a stored procedure called sp_send_dbmail in our SSIS packages. I'm not convinced it's the best approach but it works and it's in place so we do it. I was trying to diagnose an issue and had this call

EXECUTE msdb.dbo.sp_send_dbmail 
    @profile_name = 'SQLPROFILE'
,   @recipients = 'someone@somewhere.com'
,   @file_attachments = 'K:\data\somefile.txt'
,   @subject = 'Something report'
,   @body = 'Attached is the file containing something.'

It errored out with the following

Msg 22051, Level 16, State 1, Line 0
Attachment file D:\SSISData\Concur\Output\ConcurBlankSupervisorReport.csv is invalid.

I copied that path and in the open file dialog from notepad pasted that in. The file opened fine. I reran the stored procedure with C instead of the K drive and voila, it works like a champ. My initial interblag browsings found someone having issues using a file on a networked drive getting the same error so maybe the stored proc isn't as wise as defrag. Oh well, plenty of work to do but thought I'd capture this in case it helps someone. I'm sure BOL documents this behaviour but it's not on my critical path at the moment.