USING XP_FILEEXIST TO IDENTIFY WHETHER A FILE EXISTS V2K

An obscure but useful extended stored procedure is xp_fileexist. 
Located
in the master database, xp_fileexist assumes the same permissions as
most other extended stored procedures and is used, as the name implies, 
to
identify whether or not a file exists.

Execute something similar to the following to detect whether a file
and/or directory exists (Note that this also works with UNC 
connections.):

exec master.dbo.xp_fileexist 'c:\temp\LoadMe.dat'
exec master.dbo.xp_fileexist
'\\sslt02\c$\temp\LoadMe.dat'

Assume that the database server application polls for a file that
downloads nightly from another system. A bulk copy process is used to 
load this
"flat file" into the database table once the file is received. If this
procedure is integrated within the application, use programming logic 
to
analyze the xp_fileexist output values to determine whether or not to 
load
the file.

Look for the file and directory C:\Temp\LoadMe.dat. If they exist, the
xp_fileexist stored procedure returns three columns: "File Exists", 
"File
is a Directory", and "Parent Directory Exists". Each will be assigned
either a "1" or "0" dependent on whether the file does or does not 
exist. If
the file exists, "1" will appear in the column; if it does not exist,
"0" will appear in the column. These values can then be placed into a 
table
using the following syntax:

CREATE TABLE #Temp (
[File Exists] int null,
[File is a Directory] int null,
[Parent Directory Exists] int null)
GO

The output supplies data regarding the file's existence as well as
directory information. Act upon this output by following this simple 
guide:

INSERT INTO #Temp EXECUTE xp_fileexist 'c:\temp\LoadMe.dat'
GO
SELECT * FROM #Temp
GO

Finally, query the #Temp table for the values and make a logical
determination on how to proceed.

----------------------------------------