By: Jeremy Kadlec | Comments (6) | Related: More > Scripts
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!
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
