June 10, 2004 at 8:46 am
I'll try to reduce this to the simplest possible form to make the point easier to understand. I have a table called "Files". The purpose of this table is to "attach" files to a record in a parent table. In the Files table are an ID, a filename, a file extension, and a foreign key called parentID. So it looks like this...
fID Filename Extension ParentID
1 document doc 12
2 spreadsheet xls 13
3 textfile txt 12
4 document doc 19
Each of these records represents a file in a single directory on the network. The path to that network directory is available from another table in the database in a single record (a "SysPrefs" table where I tell the system the path to the attached files). The names of those files are 1.tcf, 2.tcf, 3.tcf, and 4.tcf (tcf is the generic file extension I'll be using). I want to do it this way so that duplicate filenames aren't a problem, and so that the nature of the files remains ambiguous to snooping users. Here's the question...
Let's say I delete parent record 13, so I also issue the statement:
DELETE FROM Files WHERE ParentID = 13
Is there an easy way that the database can - given the information available for each of those records - delete the file on the network automatically whenever the corresponding record in the files table is deleted? For the example above, therefore, I would want to delete 1.tcf and 3.tcf. I was thinking of using a trigger - but I'm not sure how to issue a command from SQLServer that can delete a file at a network location. Does anyone know if this definitely is or is not possible?
Thanks,
Zac
June 10, 2004 at 10:03 am
How about creating 3 variables
@filepath and @filename, @ActionCmd
set @filepath = the file path information from your sysprefs table
set @filename = the filename information from your files table
set @ActionCmd = 'DEL ' + @filepath + @filename
EXEC master..xp_cmdshell @ActionCmd
You may need to use a CURSOR or other mechanism to walk the files to be deleted from the physical environ. But, hopefully this gives you an idea
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 10, 2004 at 10:17 am
Hi, I thought I was smart when I made my avatar my baby sone.... guess you thought of it too...
anyway, for your problem Yes it can be done with xp_cmdshell. this extended stored proc gives you a dos shell to use you just past a string as the commands you want to use.
I would be a little nervous about deleting files however as if your users are as....hmm...how do i say....stupid as mine they will want an undelete. I have to build everything with a bit column 'IsInactive' and then tggle it for deletes.
as for all of the files on your filesystem I think i would develop a purge procedure that ran on an interval so that the user with an oops could get the data back.
now on to the procedure
I would make a cursor to scroll through your files. then call xp_cmdshell
Declare @STR Varchar(200)
Set @STR='del ' + @yourfile
exec master..xp_cmdshell @STR
Hope that helps
tal mcmahon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply