September 9, 2008 at 9:04 am
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
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
September 10, 2008 at 4:58 am
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
Glad to help. As with all things, practice makes perfect.
_____________________________________________________________________
- Nate
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply