Friday, February 17, 2012

EXECUTE master.dbo.xp_delete_file

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 files

DECLARE @.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