Can anyone help me with a script which would delete files or
move them to a different folder at some scheduled time..!
Please....!!!
Thanks in advance...Raj (rjdave@.indiatimes.com) writes:
> Can anyone help me with a script which would delete files or
> move them to a different folder at some scheduled time..!
Which files? MDF, LDF and NDF files? Or just any random selected files?
In any case, you could use SQL Server Agent to create a CmdExec job for
the task. You can access SQL Server Agent from Enterprise Manager.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi
The check for the date in your cursor declaration is probably the cause of
the problem!
Declare mycursor cursor for
select name from #Filenames
where convert(datetime,left(name,10)) <= getdate()-@.duration
Although you can add a style to the convert function, this is not guaranteed
to give the correct results.
From BOL:
style
Is the style of date format used to convert datetime or smalldatetime data
to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data
types), or the string format when converting float, real, money, or
smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or
nvarchar data types).
i.e no mention of use of style when converting from character to datetime
A less risky option would be to convert both to strings and then re-arrange
them into an alphabetically comparable format such as YYYYMMDD. Overall I
think the DTS/Filesystemobject may well be the faster and safer solution.
John
"Raj" <rjdave@.indiatimes.com> wrote in message
news:c11c051e.0307120602.4fe8b9c7@.posting.google.c om...
> Erland Sommarskog <sommar@.algonet.se> wrote in message
news:<Xns93B6B78068CAYazorman@.127.0.0.1>...
> > Raj (rjdave@.indiatimes.com) writes:
> > > Can anyone help me with a script which would delete files or
> > > move them to a different folder at some scheduled time..!
> > Which files? MDF, LDF and NDF files? Or just any random selected files?
> > In any case, you could use SQL Server Agent to create a CmdExec job for
> > the task. You can access SQL Server Agent from Enterprise Manager.
> Thanks ...I didn't know about it ... It was great help. But still it
> didn't work . I wanted to run a job and the script is below . I am not
> able to run this script using sql server agent. Can anyone suggest
> whats wrong ...and whats the trick i am missing?
> Create procedure USP_DelOldFiles @.path varchar(25),@.duration int
> as
> --Objective: To delete files older than certain period from a folder
> --Usage example:
> --Exec USP_DelOldFiles 'c:\test',30 -- which deletes files older than
> todaydate-30
> --Created by :MAK
> --Created date: Jan 7,2003
> --OS: windows 2000
> declare @.myquery varchar(1000)
> declare @.query varchar(1000)
> declare @.name varchar(100)
> set @.myquery = "exec master.dbo.xp_cmdshell 'dir "+
> ltrim(rtrim(@.path)) + "\*.* /a/od'"
> print @.query
> create table #Filenames (id int identity(1,1) ,name varchar(100))
> insert #Filenames(name)
> exec (@.Myquery)
> delete from #Filenames where substring(name,3,1) <> '/' or name is
> null or
> substring(name,25,1) ='<'
> Declare mycursor cursor for
> select name from #Filenames where
> convert(datetime,left(name,10)) <= getdate()-@.duration
> open mycursor
> fetch next from mycursor into @.name
> while (@.@.fetch_status =0! )
> begin
> set @.query = 'exec master.dbo.xp_cmdshell "del '+@.path+'\'+
> ltrim(rtrim(substring(@.name,40,59)))+'"'
> --print @.query
> exec (@.query)
> fetch next from mycursor into @.name
> end
> close mycursor
> deallocate mycursor
> drop table #Filenames|||Raj (rjdave@.indiatimes.com) writes:
> Thanks ...I didn't know about it ... It was great help. But still it
> didn't work . I wanted to run a job and the script is below . I am not
> able to run this script using sql server agent. Can anyone suggest
> whats wrong ...and whats the trick i am missing?
Hey, what about *you* tell us what is wrong? I mean, you say that you
are not able to run it, but rather let us guess what you mean with that,
why not specify what you. Do you get an error message? Do the script
end without an files being deleted? Did the script work from Query
Analyzer?
What I can see at a glance is:
> while (@.@.fetch_status =0! )
Syntax error.
> set @.query = 'exec master.dbo.xp_cmdshell "del '+@.path+'\'+
> ltrim(rtrim(substring(@.name,40,59)))+'"'
Should have a /F to force deletion of read-only files.
Really why you make the entire call to xp_cmdshell in dynamic SQL, I
don't know, neither I have cared to check that you get the right statement.
It should be sufficient to have only the DOS command in a variable.
Then again, why SQL at all? Isn't this a symptom of that when all you
have is hammer, everything looks like nails to you. Personally I would
implement this in Perl, but if you VB script or C++, these languages
work equally well for the task. SQL is probably one of more complicated
options you can go for.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi
Check out the method in my previous posts, your procedure should work if you
corrected the date formats and typos.
Note the escaped quotes in the command string, rather than using double
quotes.
Create procedure USP_DelOldFiles @.path varchar(25),@.duration int
as
--Objective: To delete files older than certain period from a folder
--Usage example:
--Exec USP_DelOldFiles 'c:\test',30
-- which deletes files older than todaydate-30
--Created by :MAK
--Created date: Jan 7,2003
--OS: windows 2000
declare @.myquery varchar(1000)
declare @.query varchar(1000)
declare @.name varchar(100)
set @.myquery = 'exec master.dbo.xp_cmdshell ''dir '+ ltrim(rtrim(@.path)) +
'\*.* /a/od'''
print @.query
create table #Filenames (id int identity(1,1) ,name varchar(100))
insert #Filenames(name)
exec (@.Myquery)
delete from #Filenames
where substring(name,3,1) <> '/'
or name is null
or substring(name,25,1) ='<'
select name, SUBSTRING(name,7,4) + SUBSTRING(name,4,2) + left(name,2)
from #Filenames
/* Make sure dates are in comparable formats */
Declare mycursor cursor for
select name from #Filenames
where SUBSTRING(name,7,4) + SUBSTRING(name,4,2) + left(name,2) <=
CONVERT(char(8),DATEADD(d,@.duration,getdate()),112 )
open mycursor
fetch next from mycursor into @.name
while @.@.fetch_status = 0
begin
set @.query = 'exec master.dbo.xp_cmdshell ''del '+ @.path + '\'+
ltrim(rtrim(substring(@.name,40,59)))+''''
print @.query
exec (@.query)
fetch next from mycursor into @.name
end
close mycursor
deallocate mycursor
drop table #Filenames
This may also be useful for other things.
http://vyaskn.tripod.com/oracle_sql...equivalents.htm
http://msdn.microsoft.com/library/d...asp?frame=true
John
"Raj" <rjdave@.indiatimes.com> wrote in message
news:c11c051e.0307130220.6fae6dc3@.posting.google.c om...
> Hi ... Sorry but I have to clarify a few things about me first...I am
> a newbie in database administration . I am familiar with many things
> which are Oracle related but for a particular task our company is
> using SQLSERVER 2000. I am lost in this SQL SERVER world . I have only
> one task to be finished with SQLSERVER 2000 . I am slowly making "new
> discoveries" of this database software. Lots of thanks specially to
> YOU Mr.Erland Sommarskog.
> Now the specifics of my task. I want to schedule a job by the
> database software which would delete the files in some folder based on
> the date. The dates of the files would be associated with createddate
> and expirydate columns in a database table i.e., when a file is
> uploaded to a directory then the date info would be inserted in the
> database table column and when the file's reference is deleted in the
> corresponding column a job scheduled to run at some interval of days
> should delete the physical files (*.txt,*.dat..etc) . Hope this make
> something clear for you. Sorry IF I sound stupid but am just earning
> my bread .
> Thanks for the help so far and best wishes.
> Erland Sommarskog <sommar@.algonet.se> wrote in message
news:<Xns93B6F1164E422Yazorman@.127.0.0.1>...
> > Raj (rjdave@.indiatimes.com) writes:
> > > Thanks ...I didn't know about it ... It was great help. But still it
> > > didn't work . I wanted to run a job and the script is below . I am not
> > > able to run this script using sql server agent. Can anyone suggest
> > > whats wrong ...and whats the trick i am missing?
> > Hey, what about *you* tell us what is wrong? I mean, you say that you
> > are not able to run it, but rather let us guess what you mean with that,
> > why not specify what you. Do you get an error message? Do the script
> > end without an files being deleted? Did the script work from Query
> > Analyzer?
> > What I can see at a glance is:
> > > while (@.@.fetch_status =0! )
> > Syntax error.
> > > set @.query = 'exec master.dbo.xp_cmdshell "del '+@.path+'\'+
> > > ltrim(rtrim(substring(@.name,40,59)))+'"'
> > Should have a /F to force deletion of read-only files.
> > Really why you make the entire call to xp_cmdshell in dynamic SQL, I
> > don't know, neither I have cared to check that you get the right
statement.
> > It should be sufficient to have only the DOS command in a variable.
> > Then again, why SQL at all? Isn't this a symptom of that when all you
> > have is hammer, everything looks like nails to you. Personally I would
> > implement this in Perl, but if you VB script or C++, these languages
> > work equally well for the task. SQL is probably one of more complicated
> > options you can go for.|||Raj (rjdave@.indiatimes.com) writes:
> Now the specifics of my task. I want to schedule a job by the
> database software which would delete the files in some folder based on
> the date. The dates of the files would be associated with createddate
> and expirydate columns in a database table i.e., when a file is
> uploaded to a directory then the date info would be inserted in the
> database table column and when the file's reference is deleted in the
> corresponding column a job scheduled to run at some interval of days
> should delete the physical files (*.txt,*.dat..etc) . Hope this make
> something clear for you. Sorry IF I sound stupid but am just earning
> my bread .
Still makes more sense to write a program in VBscript, Perl or
whatever that reads the required information from the database, and
then deletes the files. Running that program could possible be scheduled
from SQL Server Agent.
But running the whole thing from T-SQL with xp_cmdshell is not very
robust, becuase you need handle dateformats that that kind of thing.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
No comments:
Post a Comment