in SQL 2000 delete photos in the file system files

Hello there,
I am trying to delete unused photos in the server file system according to a table's key. I have test to delete single file with the following T-Sql script and it worked, but when I create a cursor to run the batch, it did not delete anything.
Is there a better approach for this kind of task?
Please help. Thanks .
declare abc cursor for
 
select [key]
from tempPhotoZ
order by [key]
 
open abc
declare @skey varchar(15)
declare @str varchar(200)
set @str='DEL e:\Dailypull\photographs\'
Fetch Next from abc into @skey
 
While @@Fetch_status =0
Begin
set @str=@str+@skey+'*.jpg'
 
Execute master.dbo.xp_cmdshell @str
Fetch next from abc into @skey
End
 
close abc
deallocate abc

Open in new window

LVL 1
tomcattyyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dodge20Commented:
What version of windows? In 2003 there is a forfiles command that works great.

http://technet.microsoft.com/en-us/library/cc753551(WS.10).aspx
0
tomcattyyAuthor Commented:
It's windows 2003 and I am checking the link. thanks
0
tomcattyyAuthor Commented:
Hello dodge20,

use forfiles do I still need the cmd del ? Can you point out what is wrong with my script above?
Thanks
0
dodge20Commented:
Yes you still need the del.

I would put a select @str after your set statement to see what the actual command is getting passed to xp-cmdshell.

Also I assume  xp-cmdshell is enabled?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tomcattyyAuthor Commented:
Yes the xp_cmdshell is enabled.
I found the @str first time is correct and the next run only makes the @str longer by adding the new photo number.
first run
DEL e:\Dailypull\photographs\90022782129*.jpg
then
DEL e:\Dailypull\photographs\90022782129*.jpg9025655912*.jpg
....continue.
I change the code and this time the select @str shows it is correct line by line
Thanks for your help

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.