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)
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 its 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.