xp_fileexist Fails Even if Mapped to a Local Folder

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.)

Determining if a Folder Exists Using xp_cmdshell

The code below creates a stored proc that will report if a passed-in Windows folder exists or not. While it avoids using the undocumented xp_fileexist system proc, it uses xp_cmdshell, which in many places is the first thing that gets disabled because of the obvious security ramifications. Still, it's a nice hack if you're working in the file system a lot.

IF OBJECT_ID('dbo.usp_FolderExist') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.usp_FolderExist
END
 
GO
 
CREATE PROCEDURE dbo.usp_FolderExist (@FullPathName NVARCHAR(255),
                                      @Exists       TINYINT OUTPUT)
AS
BEGIN
    SET NOCOUNT ON
   
    -- Remove any trailing slash.
    DECLARE @TrailingSlash NVARCHAR(255)
    SET @TrailingSlash = REVERSE(@FullPathName)
    
    IF '\' = LEFT(@TrailingSlash, 1) 
        SET @TrailingSlash = SUBSTRING(@TrailingSlash, 2, LEN(@FullPathName) + 1)
  
    SET @FullPathName = REVERSE(@TrailingSlash)
   
    -- Build and exec the command.
    DECLARE @Cmd NVARCHAR(4000)
    SET @Cmd = 'dir ' + @FullPathName
    SET @Exists = 0
    CREATE TABLE #dir (output NVARCHAR(4000))
 
    INSERT INTO #dir
        EXEC master..xp_cmdshell @Cmd
 
    IF EXISTS (SELECT * FROM #dir WHERE output LIKE '%' + @FullPathName + '%')
        SET @Exists = 1
      
    -- Done.
    DROP TABLE #dir
END
 

/* Test Code
 
set nocount on
declare @bExists int   set @bExists = 0
exec usp_FolderExist 'C:\Temp',  @bExists OUTPUT
print @bExists
exec usp_FolderExist 'C:\Temp\', @bExists OUTPUT
print @bExists
 
*/