January 28, 2008 at 1:07 pm
Hi,
Every hour i have files that get copied from one location to another and renamed with adding a date and time to the file name (FileName_200801281130.txt).
I need to be able to delete files that are older then 48 hours. i looked at the previous posts and there are only refer to deleting the files older then x number of day, but i need hours...
Please help! 🙂
January 28, 2008 at 2:58 pm
If you're doing this in T-SQL, the only way I know of to do this is use xp_CmdShell to insert a list of the file names into a temp table, parse out the date and time from the file name, then create a dynamic SQL command using xp_CmdShell to delete each file.
Since xp_CmdShell is a security hole all by itself, I can't recommend using that method. I think you'd be better off using a language that's designed to work with the file system directly, as opposed to T-SQL. (This post is in the T-SQL forum.) I haven't programmed in VB for a while, but I'm pretty sure it could do all that pretty efficiently.
Even an SSIS package would be safer than xp_CmdShell.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2008 at 3:52 pm
I agree with GSquared. While it is possible to do this via xp_cmdshell, I would not advise it. Create a vbscript or .bat file that will do the same thing, but you can scedule it via the windows sceduler.
January 30, 2008 at 10:17 am
Hi guys,
I came up with this code. and it works:)
declare @d as varchar (8)
declare @d1 as varchar (12)
DECLARE @NewFileName VarChar(30)
DECLARE @SQL_Command VarChar(200)
DECLARE @SQL_Command1 VarChar(200)
DECLARE @SQL_Command2 VarChar(200)
DECLARE @SQL_Command3 VarChar(200)
DECLARE @BACKUP_DATE as VARCHAR(64)
DECLARE @BACKUP_DATE1 as VARCHAR(64)
DECLARE @BACKUP_DATE2 as VARCHAR(64)
SET @BACKUP_DATE = cast(year(getdate()) as varchar(4))+substring('0',1,2-len(cast(month(getdate())
as varchar(4))))+cast(month(getdate()) as varchar(4))+substring('0',1,2-len(cast(day(getdate())
as varchar(4))))+cast(day(getdate()) as varchar(4))+ SUBSTRING(CONVERT(Char,GetDate(),120),12,2)
-- + SUBSTRING(CONVERT(Char,GetDate(),120),15,2)
--print @BACKUP_DATE
SET @BACKUP_DATE1 = cast(year(getdate()) as varchar(4))+substring('0',1,2-len(cast(month(getdate())
as varchar(4))))+cast(month(getdate()) as varchar(4))+substring('0',1,2-len(cast(day(getdate()-3)
as varchar(4))))+cast(day(getdate()-3) as varchar(4))
--+ SUBSTRING(CONVERT(Char,GetDate(),120),12,2)
--+ SUBSTRING(CONVERT(Char,GetDate(),120),15,2)
SET @BACKUP_DATE2 = cast(year(getdate()) as varchar(4))+substring('0',1,2-len(cast(month(getdate())
as varchar(4))))+cast(month(getdate()) as varchar(4))+substring('0',1,2-len(cast(day(getdate()-2)
as varchar(4))))+cast(day(getdate()-2) as varchar(4))+ SUBSTRING(CONVERT(Char,GetDate(),120),12,2)
--+ SUBSTRING(CONVERT(Char,GetDate(),120),15,2)
--print @BACKUP_DATE1
--xp_cmdshell 'FORFILES /p w:\Backups\ESSBASE /m *.* /s /c "CMD /C del @FILE" /d -2'
SET @SQL_Command = 'FORFILES /p w:\Backups\ESSBASE /m ESSBASE_' + @BACKUP_DATE1 + '*.bak /s /c "CMD /C del @FILE"'
SET @SQL_Command1 = 'xp_cmdshell ''' +@SQL_Command+''''
print @SQL_Command1
EXEC (@SQL_Command1)
SET @SQL_Command2 = 'FORFILES /p w:\Backups\ESSBASE /m ESSBASE_' + @BACKUP_DATE2 + '*.bak /s /c "CMD /C del @FILE"'
--print @SQL_Command
SET @SQL_Command3 = 'xp_cmdshell ''' +@SQL_Command2+''''
print @SQL_Command3
EXEC (@SQL_Command3)
-----------------
I have anothe issue now. I have tried to do the same with xp_delete_file and i can not make it work. I will probably add a separate post for this issue:)
January 30, 2008 at 2:35 pm
Well - now that you have SQL server doing your file maintenance - any chance you can figure out how it can turn the coffee on in the morning? :D:P;):w00t::cool::hehe:
Now THERE would be a feature....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 31, 2008 at 8:58 am
Since you handled the maintenance part, here's a little SSIS app that actually watches folders for changes and returns file names. It allows you to set a wait period as well. Not sure if it will help you, but you can check it out at: http://www.sqlis.com/23.aspx
January 31, 2008 at 11:16 pm
Matt Miller (1/30/2008)
Well - now that you have SQL server doing your file maintenance - any chance you can figure out how it can turn the coffee on in the morning? :D:P;):w00t::cool::hehe:Now THERE would be a feature....
Heh... POKE the game port to fire an opto-coupled TRIAC...
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2008 at 11:39 am
I agree with the posts that say you should use a programming language to do this. But if you don't have programming software and have to use SQL Server 05 and you have SSIS at your disposal (ships with SQL 05), then I'd use this tool to do this. It's meant for it and probably only take 3 or 4 SSIS tasks to do what you need to do.
You can also deploy your SSIS package to a server and schedule a job to run this every hour as well.
Thanks,
Strick
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply