Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


delete files in a directory older than a month

Posted on 2006-04-20
Medium Priority
Last Modified: 2012-06-27
I am attempting to create a sql command that will go to a given directory and delete out all files older than a month.

How do i do this?
Question by:DJMoonLight
LVL 27

Accepted Solution

ptjcb earned 500 total points
ID: 16500569
You could do this much easier with a windows script than t-sql. For example: http://www.databasejournal.com/scripts/article.php/3517231
You could schedule it as a job and run it as a CMDEXEC.

To use t-sql you would have to use a filesystem object and DMO. Lots of code...

I found this at this link (I have not tried it - I use VBScript):http://www.thescripts.com/forum/thread78656.html

Create procedure USP_DelOldFiles @path varchar(25),@duration int
--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
set @query = 'exec master.dbo.xp_cmdshell ''del '+ @path + '\'+
print @query
exec (@query)
fetch next from mycursor into @name
close mycursor
deallocate mycursor

drop table #Filenames

Expert Comment

ID: 16500662
I have to agree... Using the windows scheduler and then a simple utility like delOld.exe would be much simpler.


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

580 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