File Access

  • 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

  • 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

  • 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

     

     


    Kindest Regards,

    Tal Mcmahon

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply