I need to delete a file in a job as a step on a different server.
is there something simillar to this command in sql 2000?
EXECUTE master.dbo.xp_delete_file 0,N'D:\DevBack',N'bak',N'10/16/2006 15:16:22'
and what will the command look like if I need to delete only files older than 3 days?
please help
the command goes across the network
like this
EXECUTE master.dbo.xp_delete_file 0,N'\\100\00\00\02\DevBack',N'bak',N'10/16/2006 15:16:22'
|||--builds the date stamp to reflect the stamp added to the filesDECLARE @.dt datetime
DECLARE @.month varchar(2)
DECLARE @.day varchar(2)
SELECT @.dt=getdate()-1 -- 1 is the files with 1 day old
DECLARE @.builtdate varchar(8)
--this ensures a two digit value in month and day
if(len(MONTH(@.dt)) = 1 )
SET @.month = convert(varchar(2), '0' + convert(varchar(1), MONTH(@.dt)))
else
SET @.month = MONTH(@.dt)
if(len(DAY(@.dt)) = 1 )
SET @.day = convert(varchar(2), '0' + convert(varchar(1), DAY(@.dt)))
else
SET @.day = DAY(@.dt)
--this puts it all together and builds the file name match
SET @.builtdate = convert(varchar(8), convert(varchar(4),YEAR(@.dt)) + @.month + @.day)
declare @.filename varchar(100)
-- this removes all .BAK that contain the datestamp in filename
set @.filename = 'del D:\somefilepath\*'+@.builtdate+'*.BAK'
exec xp_cmdshell @.filename
-- this removes all .TRN that contain the datestamp in filename
set @.filename = 'del D:\somefilepath\*'+@.builtdate+'*.TRN'
exec xp_cmdshell @.filename
No comments:
Post a Comment