File Validation in SQL Server with xp_fileexist stored procedure

http://www.mssqltips.com/sqlservertip/1272/file-validation-in-sql-server-with-xpfileexist-stored-procedure/

ProblemIn a recent tip (Accessing the Windows File System from SQL Server) options were outlined to capture files from a specific directory into a temporary table or table variable for additional processing.  A similar file operations task that is needed in particular T-SQL code is to validate a specific file exists or not.  Then logic can be written to either process the file, retry at a specific interval or fail the process.  As such, how can this be accomplished and can you provide me with some concrete examples in SQL Server 2000 and 2005?
SolutionOne option to validate that a file exists or not is to use the master.dbo.xp_fileexist extended stored procedure.  In a nutshell this extended stored procedure will validate that a file exists.  Unfortunately, master.dbo.xp_fileexist is not a documented extended stored procedure in SQL Server Books Online.  As such, be aware that the functionality may change over time, although to the best of my knowledge this extended stored procedure has maintained its functionality between SQL Server 2000 and 2005.  Nevertheless, let's jump into a few examples to see how this code behaves in SQL Server 2000 and 2005.

Example 1 - Execute master.dbo.xp_fileexist without any parameters Version - SQL Server 2000 and SQL Server 2005

EXEC Master.dbo.xp_fileexist GO

Server: Msg 22027, Level 15, State 1, Line 0 Usage: EXECUTE xp_fileexist <filename> [, <file_exists INT> OUTPUT]

Example 2 - Execute master.dbo.xp_fileexist with valid file and directory with the results indicating success for both the file and directory, in addition the number of rows is accurately suppressed with the NOCOUNT command  Version - SQL Server 2000 and SQL Server 2005

SET NOCOUNT ON DECLARE @FileName varchar(255) SELECT @FileName='C:\boot.ini' EXEC Master.dbo.xp_fileexist @filename GO

File Exists   File is a Directory  Parent Directory Exists ----------- ------------------- ----------------------- 1                0                            1

Example 3 - Execute master.dbo.xp_fileexist without a valid file and directory with the results indicating failure for both the file and directory, in addition the number of rows is accurately suppressed with the NOCOUNT command Version - SQL Server 2000 and SQL Server 2005

SET NOCOUNT ON DECLARE @FileName varchar(255) SELECT @FileName='Z:\bootdoesnotexist.ini' EXEC Master.dbo.xp_fileexist @filename GO

File Exists   File is a Directory  Parent Directory Exists ----------- ------------------- ----------------------- 0                0                            0

Example 4 - Execute master.dbo.xp_fileexist with a valid file and directory with the results indicating 'File Found', in addition the number of rows is inaccurately suppressed with the NOCOUNT command in SQL Server 2000 and accurately in SQL Server 2005 Version - SQL Server 2000 and SQL Server 2005

SET NOCOUNT ON DECLARE @FileName varchar(255) DECLARE @File_Exists int SELECT @FileName='C:\boot.ini' EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT IF @File_Exists = 1 PRINT 'File Found' ELSE PRINT 'File Not Found' GO

SQL Server 2000 (1 row(s) affected) File Found SQL Server 2005 File Found

Example 5 - Execute master.dbo.xp_fileexist with an invalid file and directory with the results indicating 'File Not Found', in addition the number of rows is inaccurately suppressed with the NOCOUNT command in SQL Server 2000 and accurately in SQL Server 2005 Version - SQL Server 2000 and SQL Server 2005

SET NOCOUNT ON DECLARE @FileName varchar(255) DECLARE @File_Exists int SELECT @FileName='C:\boot.ini' EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT IF @File_Exists = 1 PRINT 'File Found' ELSE PRINT 'File Not Found' GO

SQL Server 2000 (1 row(s) affected) File Not Found SQL Server 2005 File Not Found

Example 6 - Execute master.dbo.xp_fileexist with a valid file and directory with the results stored in a temporary table Version - SQL Server 2000 and SQL Server 2005

SET NOCOUNT ON DECLARE @FileName varchar(255) CREATE TABLE #File_Results ( File_Exists int, File_is_a_Directory int, Parent_Directory_Exists int ) SELECT @FileName='C:\boot.ini' INSERT INTO #File_Results (File_Exists, file_is_a_directory, parent_directory_exists) EXEC Master.dbo.xp_fileexist @filename SELECT * FROM #File_Results DROP TABLE #File_Results GO

File Exists   File is a Directory  Parent Directory Exists ----------- ------------------- ----------------------- 1                0                            1

Example 7 - Execute master.dbo.xp_fileexist with an invalid file and directory with the results stored in a temporary table Version - SQL Server 2000 and SQL Server 2005

SET NOCOUNT ON DECLARE @FileName varchar(255) CREATE TABLE #File_Results ( File_Exists int, File_is_a_Directory int, Parent_Directory_Exists int ) SELECT @FileName='Z:\bootdoesnotexist.ini' INSERT INTO #File_Results (File_Exists, file_is_a_directory, parent_directory_exists) EXEC Master.dbo.xp_fileexist @filename SELECT * FROM #File_Results DROP TABLE #File_Results GO

File Exists   File is a Directory  Parent Directory Exists ----------- ------------------- ----------------------- 0                0                            0

Next Steps

• When you have the need to determine if a file exists or not, consider this tip as one means to do so.  Just keep in mind that the functionality may differ slightly between SQL Server 2000 and 2005.
• Keep in mind that the native result set may be a good indicator if a file exists.  So first check for the native result prior to building a custom need.  If you can save yourself the time, you might as well.
• Unfortunately, the Master.dbo.xp_fileexist extended stored procedure is undocumented.  As such, be sure to test appropriately with this code and validate the functionality as you apply hot fixes, patches and upgrade to the latest SQL Server version.
• Check out these related tips:
  • Accessing the Windows File System from SQL Server
• Special thanks is extended to Rudy Komacsar of the MSSQLTips.com for contributing these scripts to the community!