Problem
In 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?
Solution
One 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 |
| 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 |
SET NOCOUNT ON |
| 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 |
| 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 |
| SET NOCOUNT ON DECLARE @FileName varchar(255) DECLARE @File_Exists int SELECT @FileName=’C:\boot.ini’ |
SQL Server 2000
SQL Server 2005
|
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 |
| SET NOCOUNT ON DECLARE @FileName varchar(255) DECLARE @File_Exists int SELECT @FileName=’C:\boot.ini’ |
SQL Server 2000
SQL Server 2005
|
Example 6 – Execute master.dbo.xp_fileexist with a valid file and directory with the results stored in a temporary table |
| 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 |
| 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:
- Special thanks is extended to Rudy Komacsar of the MSSQLTips.com for contributing these scripts to the community!
Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.