I was using xp_fileexist to perform a couple of file and folder checks and ran across one of the things that you have to be careful of when using undocumented extended stored procedures. 

In this case when I was trying to insert the results into a table variable I was getting an error:

That is particularly unhelpful. You can reproduce this error yourself by running:

DECLARE @Folder NVARCHAR(MAX) = ‘C:Backup’
DECLARE @FolderCheck TABLE
    (
      FileExists BIT ,
      FileIsDir BIT ,
      ParentDirExists BIT
    );

INSERT  INTO @FolderCheck
        ( FileExists ,
          FileIsDir ,
          ParentDirExists
            
        )
        EXEC MASTER.dbo.xp_fileexist @Folder;
SELECT  FileExists ,
        FileIsDir ,
        ParentDirExists
FROM    @FolderCheck;

In a basic troubleshooting effort I tried to run the xp_fileexist without the insert into the table variable and got another error, this one being a little more helpful at first appearance.

DECLARE @BackupDirectory NVARCHAR(MAX) = ‘C:Backup’;
EXEC MASTER.dbo.xp_fileexist @BackupDirectory;

I knew that I was passing in the variable and it was valid, this just didn’t make a lot of sense. I decided to change the datatype of the variable in the hopes that it would work:

DECLARE @BackupDirectory NVARCHAR(8000) = ‘c:BackupNewFolder’;
EXEC MASTER.dbo.xp_fileexist @BackupDirectory;

So, finally an error that gave me actionable information. By changing the length of the variable to a nvarchar(4000) I was able to get the results I was looking for.

This is just one of those examples where you have to be careful what you are playing with and play around to get what you are looking for. Undocumented xp’s can be really useful, but troubleshooting them can also be a nightmare.