Check Date Stamp and Delete Old File

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Check Date Stamp and Delete Old File

Author

Topic

sqlquest2575
Starting Member

2 Posts

Posted - 2011-06-17 : 23:39:54

Hello Friends...I would really appreciate if you could help me in solving a sql problem....
I've 4 text files and all of them have date stamp in them and i want to keep 3 recent files and delete the oldest file. I've a script to delete all files from the folder but not sure how add retention in the Script

File Sample :-
TeraOutput2011-05-01T103109.567.txt
TeraOutput2011-05-07T103109.767.txt
TeraOutput2011-05-14T103109.098.txt
TeraOutput2011-05-21T103109.654.txt

My Script

use Master
go
Declare @sql varchar(250)
set @sql='Exec master.dbo.xp_cmdshell ''Del /Q f:\test\'''
Exec (@sql)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-18 : 06:48:41

If all the file names are in the same format you can use a string sort (which would effectively order them by the datestamp part of the filename) and then delete the first.

sqlquest2575
Starting Member

2 Posts

Posted - 2011-06-18 : 15:47:25

I am not really good writing tsql as i am still learning..Could you please help me here writing this code. Just to give you little more detail about this issue...I'll be running an automatic job which will create these files in a folder and i need to delete them automatically through tsql after checking the datestamp in the file. Please Help !!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-18 : 18:03:21

Usually when I post replies, I am not nervous, but in this case I am. For two reasons:

a) When I want to delete something, be it files, or rows from a table, I am always nervous. I read the command two times or three times, hold down the execute button one more time and read it again etc.

b) My expertise in DOS commands is very limited. So the DOS commands that I writing here may not be exactly what you need, or there may be better and safer ways to do it.

If you run this code as it is, it does not delete anything - it just prints out a statement. If you think that statement is correct, then uncomment the line that actually executes it.

With all those caveats, here is the code:

if object_id('tempdb..#tmp') is not null drop table #tmp;
create table #tmp ([file_name] varchar(255));

-- Get the filenames. Change the dir command to get only the files you want to consider.
insert into #tmp 
exec master.dbo.xp_cmdshell 'dir /b /A-D C:\YourFolder'


declare @sql nvarchar(511);

-- Change the folder to match your folder in the dir command above.
select top 1 
          @sql = 'exec master.dbo.xp_cmdshell ''del C:\YourFolder\' + [file_name] + ''''
from      #tmp
where     [file_name] like 'TeraOutput%.txt'
order by  [file_name] asc
	
select @sql;

-- UNCOMMENT THIS LINE ONLY IF YOU ARE COMFORTABLE WITH THE COMMAND PRINTED OUT BY
-- THE PREVIOUS STATEMENT.
-- exec (@sql);

if object_id('tempdb..#tmp') is not null drop table #tmp;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources