Post reply

Deletion of TIF files using DTS Package & SP's

  • September 9, 2008 at 9:04 am

    #154706

    Hello,

    I'm relatively new to SQL and need some help to work out how I can delete image files from a directory on my server which have a filename equal to the entries in an id column in my database. The image files have been backed-up to a more secure location and the updated image location name exists in a column in the table for each entry. I'm not sure whether to try and create a DTS package or whether a stored procedure would do this or maybe a combination of the two.

    I'll give an example, advanced apologies for my VERY bad SQL...

    E.G. Image file exists in location 'C:\Images\0\1.tif'

    The SQL statement would need to be something like:

    exec master..xp_cmdshell 'del c:\Images\0\1.tif' where id in ('select * from table where id = 1 and location = 'backup'')

    The image file name will always be different as the id column is a primary key (not incremental) and the directory below the images directory will often be different.

    Would I have to declare a parameter of some kind as the id will always be different.

    Hope this makes sense. If you need to know, I'm using SQL 2000 on MS Windows Server 2000.

    Thanks for any help!

  • September 9, 2008 at 3:17 pm

    #869324

    This code snippet might work for you.

    create table #tbl (imagefile varchar(50), location varchar(50))

    declare @filename varchar(50)

    , @sql varchar(255)

    insert into #tbl (imagefile, location) values ('C:\Images\0\1.tif', 'backup')

    insert into #tbl (imagefile, location) values ('C:\Images\0\2.tif', 'backup')

    insert into #tbl (imagefile, location) values ('C:\Images\0\3.tif', 'backup')

    while exists (select 1 from #tbl where location = 'backup')

    begin

    select top 1 @filename = imagefile from #tbl where location = 'backup'

    select @sql = 'del ' + @filename

    select @sql

    -- exec master.dbo.xp_cmdshell @sql

    update #tbl set location = 'done' where imagefile = @filename

    end

    drop table #tbl

    If this is going to be a regular task, I'd set it up as a sql agent job.

    _____________________________________________________________________
    - Nate

    @nate_hughes

  • September 10, 2008 at 4:58 am

    #869516

    That helps a lot, I think I'll need to add some things to it to automatically populate the new table you've suggested and that's going to require a calculation or two but I think I can probably work out how to do that now that I've seen how you've put the syntax together, I wasn't sure about declaring variables you see!

    Thanks very much for your help with this problem, maybe one day I'll be able to understand SQL statements enough to help somebody out!

    Thanks again.

    Chris

  • September 10, 2008 at 5:54 am

    #869543

    Glad to help. As with all things, practice makes perfect.

    _____________________________________________________________________
    - Nate

    @nate_hughes

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

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