In many environments, security concerns prevent the enabling of xp_cmdshell. So how would one get the information without it?
If you can rely on a scheduled job, here's a solution:
Create a job with a job step that uses the Operating system (CMDEXEC) type. Create a DOS command in this step that pipes the results of a DIR to a file:
DIR d:\MyDirectory >d:\MyOutputFolder\MyDirectoryList.txt
Next, create a job step that BULK INSERTs the contents of the file to a temporary table. From this point, you can evaluate the contents of the directory. In this example, I look for the existence of a certain file, and return an error code if it doesn't exist. The job is set up to quit with success if this step fails (returning the error from RAISERROR), so it doesn't report a job failure. (In this case, I simply want to stop processing the job if the file's not there.)
declare
@sql varchar(256)
create table #filelist (fileinfo varchar(255))
set @sql = 'BULK INSERT #filelist
from '
+ '''d:\MyOutputFolder\MyDirectoryList.txt''' + ' with (DataFileType = '+ '''char''' + ', FieldTerminator = ' + ''',''' + ',
FirstRow = 1)'
exec
(@sql)
--Look for the file name in the temp table. Each record is an entry from the DIR command results.
if
NOT EXISTS(select * from #filelist where charindex('MyFile.txt',fileinfo) > 0)
BEGIN
DROP
TABLE #filelist
RAISERROR
('MyFile.txt not found.',16,1)
END
ELSE
DROP
TABLE #filelist
You can do whatever you want with the data once it's in the temporary table. Issue a DROP TABLE #filelist at the end of your code to clean up.
