Apparently the undocumented xp_fileexist procedure really doesn't like mapped drives. I knew about the headaches involved with using this proc to read network drives (permissions issues are usually the problem), but I expected it to return correct results for a locally mapped drive.
To see this, map your C:\Windows folder to, for example the W: drive, and then run this script:
-- Demonstration that xp_fileexist really, -- really doesn't like mapped drives - -- even those mapped to a local path. DECLARE @FileName VARCHAR(128) -- Local disk folder succeeds: -- File_Exists = 1 -- File_is_a_Directory = 0 -- Parent_Directory_Exists = 1 SET @FileName = 'C:\Windows\Notepad.exe' EXEC xp_fileexist @FileName -- Map a drive to \\MYMACHINE\c$\Windows as drive W: -- Local mapped drive FAILS: -- File_Exists = 0 -- File_is_a_Directory = 0 -- Parent_Directory_Exists = 0 SET @FileName = 'W:\Notepad.exe' EXEC xp_fileexist @FileName
If there's a workaround for this, I'd be interested. (Yes, I could be the 1,000,000th person to write my own CLR to do this, but I'm hoping for a better way.)