Help ---! Procedure to delete files from operating system

Raj (rj****@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...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" <rj****@indiatimes.com> wrote in message
news:c1**************************@posting.google.c om...

Erland Sommarskog <so****@algonet.se> wrote in message

news:<Xn*********************@127.0.0.1>...

Raj (rj****@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 (rj****@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...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_...quivalents.htm
http://msdn.microsoft.com/library/de...asp?frame=true

John

"Raj" <rj****@indiatimes.com> wrote in message
news:c1**************************@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 <so****@algonet.se> wrote in message

news:<Xn**********************@127.0.0.1>...

Raj (rj****@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 (rj****@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp