File Validation in SQL Server with xp_fileexist stored procedure

By:   |   Updated: 2007-06-25   |   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
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!


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec 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


Article Last Updated: 2007-06-25

Comments For This Article




Monday, March 5, 2018 - 4:52:22 AM - Navin Back To Top (75348)

Hi Kadlec,

I have a question regarding xp_fileexist.

I need to check if a file exists using a non-sa user(db_owner for master).

But when I use the xp_fileexist I get 0 for all the field values.

Could you please help me in this.

 

Query I use:

 

CREATE TABLE TEMP(FILE_EXISTS INT, FILE_IS_DIR INT, PARENT_DIR  INT)

INSERT INTO TEMP

EXEC master.dbo.xp_fileexist '<filepath>';

 

I get correct values when I run the same query with sa user.

Friday, June 28, 2013 - 1:57:46 PM - DeWitte Back To Top (25619)

Thanks for posting this - it really helped!

Friday, June 15, 2012 - 2:56:07 PM - Jeremy Kadlec Back To Top (18037)

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 Back To Top (18008)

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 Back To Top (17680)
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 Back To Top (1957)

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!














get free sql tips
agree to terms