Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

in SQL 2000 delete photos in the file system files

Posted on 2009-07-02
5
164 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql help 5 54
2 comma seperated list - SQL Server 12 43
Email Notifications for SQL 2005 9 35
What is this datetime? 1 19
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

839 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