In last week's article we discussed xp_cmdshell

This week we will cover the xp_fileexist, which will tell you if a file is in a specified directory.

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.