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
@profile_name = 'SQLPROFILE'
, @recipients = 'email@example.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.