Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

File Validation in SQL Server with xp_fileexist stored procedure

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (5)   |   Related Tips: 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
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:
  • Special thanks is extended to Rudy Komacsar of the MSSQLTips.com for contributing these scripts to the community!


Last Update: 6/25/2007


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Friday, June 28, 2013 - 1:57:46 PM - DeWitte Read The Tip

Thanks for posting this - it really helped!


Friday, June 15, 2012 - 2:56:07 PM - Jeremy Kadlec Read The Tip

Mark,

As a super simple solution, have you thought about writing a cursor to check 1 file at a time and record if the file has been copied or not in a table?

Here is a tip on cursors - http://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/.

HTH

Thank you,
Jeremy Kadlec


Thursday, June 14, 2012 - 3:45:58 PM - Mark Sawczuk Read The Tip

I have an interesting pro

So far I have found numerous posts on the web regarding the use of xp_fileexist, but everything is tied to checking whether a single file exists...

I have an exceptionally large imaging database with many TB of image files that go with it... As storing raw image data isn't a good idea for databases, I stored merely relative paths to the images in one of my tables... This keeps teh database size manageable as the number of images collected in this project continues to charge forward...

Anyway, we've had to spawn off another copy of this system on physically different hardware and copy over both the images and the database...  Regardless what methods we tried to move files, and what methods we try to use to verify that all of the raw image files were correctly copied over into this second server, I can't seem to find an effective way to check that all the files have moved over...

I was hoping that a query can be fashioned that lets me take a select statement which returns an array of paths to the raw image files are, and that a function like this lets me return another field so that I can see what's still missing after the copy?

Any suggestions on which way to take this?  The size of the problem is on the order of hundreds of thousands of images, and these are images from a medical device that are about 7 MB a piece...

I got excited when I saw this xp_fileexist function, but wasn't sure if it can get wrapped into a larger select statement to run iteratively on each file returned... (I don't care if the query takes a few hours...)  If this doesn't work, I'll just have to write a console app to do this, but I was really hoping there was a simple and elegant solution for this... it doesn't have to be long term, just to get past this hurdle...

Thanks!

Mark

 


Monday, May 28, 2012 - 4:54:40 AM - SATHYANARAYANA L Read The Tip
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 SIR EVEN I EXECUTING THE ABOVE QUIRY GETTING ERROR 'Server: Msg 2812, Level 16, State 62, Line 9 Could not find stored procedure 'Master.dbo.xp_fileexist'. I CANNOT GIVE BACKUP TO CLIENT PLEASE HELP ME IN THIS REGARD THANKING YOU l SATHYANARAYANA

Saturday, October 11, 2008 - 9:46:12 AM - GGP Read The Tip

Athough an older post,  a most excellent one.   I love the undocumented stuff.    Easy enough to do in VB but now I don't have to.  Great for checking those missing images on a website!




 
Sponsor Information