Solved

in SQL 2000 delete photos in the file system files

Posted on 2009-07-02
5
156 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:tomcattyy
  • 3
  • 2
5 Comments
 
LVL 11

Expert Comment

by:dodge20
ID: 24767491
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
 
LVL 1

Author Comment

by:tomcattyy
ID: 24767582
It's windows 2003 and I am checking the link. thanks
0
 
LVL 1

Author Comment

by:tomcattyy
ID: 24767636
Hello dodge20,

use forfiles do I still need the cmd del ? Can you point out what is wrong with my script above?
Thanks
0
 
LVL 11

Accepted Solution

by:
dodge20 earned 500 total points
ID: 24767704
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
 
LVL 1

Author Comment

by:tomcattyy
ID: 24767922
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now