Using XP_FILEEXIST

The no_output parameter after the procedure will make the results of the net send command invisible to the ISQL window. The output that is not suppressed is displayed as a varchar datafield.

To execute the xp_fileexist extended stored procedure, type the following from an ISQL window or stored procedure :

exec master..xp_fileexist 'c:\autoexec.bat'


File Exists File is a Directory Parent Directory Exists 
----------- ------------------- ----------------------- 
1           0                   1

(1 row(s) affected)

Exercise:
Suppose that a legacy system is going to deposit a file into a specific directory on a hourly basis. You would like to have a stored procedure that will detect the existence of a file in a specific directory then fire off a DTS package to convert the flat file data after it’s in the directory. If you create a SQL Server Agent job to run every 30 minutes, you could use the following code to fulfill your requirements:

 
--Create temporary table
	create table #fileexists ( 
	doesexist smallint,
	fileindir smallint,
	direxist smallint)

-- Insert into the temporary table
	Insert into #fileexists exec master..xp_fileexist 'C:\AUTOEXEC.BAT'

--Queries the temporary table to see if the file exists
    If exists (select doesexist from #fileexists FE
    where FE.doesexist = 1)
	Begin

--Executes DTS Package If File Exists
	Exec master..xp_cmdshell 'DTSRUN /S servername
	/N DTSPackageName /U sa /P password', no_output

-- Must change the above parameters to match your server requirements.
	Print 'File Does Exists and Running Package'
	End
	Else Begin
	Print 'File Does Not Exists'
	End

-- Clean up TempDB
	DROP TABLE #fileexists

	

Before you begin implementation of this extended stored procedure into your applications, you will need to be aware of the security issues. When executing xp_fileexist (as well as other extended stored procedures), you will have the same rights and permissions as whichever NT account is configured to start SQL Server (MSSQLSERVER service). This account is generally either an administrator or system account. In either case, you pose a substantial security risk if you don't lock down the extended stored procedure to not allow your non-sa users to execute it.