November 7, 2001 at 7:31 am
I have just started using SQL SERVER and could use a little help. I am trying to purge unauthorized files and folders on a daily basis from a departmental share. I have a table(TAB_FILES) that contains FILE_NAME and FILE_PATH. I would like for all files that are not listed in the TAB_FILES.FILE_NAME to be deleted from the share. I also want to delete all folders that are not listed in the FILE_PATH to be deleted. Any help would be greatly appreciated.
November 7, 2001 at 10:51 am
Not sure that sql server is the best thing to do this from - maybe a client app would be better.
You can execute any do commands using master..xp_cmdshell
to get all the files in a directory (always use a variable for comannds and print it before execution to check).
declare @cmd varchar(1000)
create table #a (s varchar(1000))
select @cmd = 'dir c:\dir\'
insert #a
exec master..xp_cmdshell @cmd
(remember that the path is relative to the server and the sql server service must have permission on the directory).
Once you have all files (look at #a and get rid of any non filename lines) you can remove the good files
delete #a where not exists (select * from TAB_FILES t where t.FILE_NAME = #a.s)
now you can delete
declare @file varchar(1000)
select @file = ''
while @file < (select max(s from #a)
begin
select @file = min(s) from #a where s > @file
select @cmd = 'del ' + @file
exec master..xp_cmdshell @cmd -- important to run this as slect @cmd first to check
end
You can do a similar thing with the directories (dir /s/B *.) but it is a bit more complicated - there is probably a better way of getting a list of all directories on the drive.
Cursors never.
DTS - only when needed and never to control.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply