This is a procedure I wrote some time ago to delete files from a fileshare older than n-days.

IF EXISTS (SELECT [name] FROM dbo.sysobjects WHERE [name] = 'usp_Admin_Delete_Files_By_Date' AND TYPE = 'P')

DROP PROCEDURE dbo.usp_Admin_Delete_Files_By_Date

GO

CREATE PROCEDURE dbo.usp_Admin_Delete_Files_By_Date (@SourceDir varchar(1024), @SourceFile varchar(512), @DaysToKeep int)

-- EXEC Admin.dbo.usp_Admin_Delete_Files_By_Date @SourceDir = '\\FooServer\BarShare\'

-- , @SourceFile = 'FooFile_*'

-- , @DaysToKeep = 3

AS

/******************************************************************************

**

**Name: usp_Admin_Delete_Files_By_Date.sql

**

**Description: Delete files older than X-days based on path & extension.

**

**Depending on the output from xp_msver, we will execute either a

**Windows 2000 or Windows 2003 specific INSERT INTO #_File_Details_02

**operation as there is a small difference in the FOR output between

**Windows 2000 and 2003 (Operating system versions).

**

**Return values: 0 - Success

**-1 - Error

**

**Author: G. Rayburn

**

**Date: 03/26/2007

**

**Depends on: xp_cmdshell access to @SourceDir via SQLAgent account.

**

*******************************************************************************

**Modification History

*******************************************************************************

**

**Initial Creation: 03/26/2007 G. Rayburn

**

*******************************************************************************

**

******************************************************************************/

SET NOCOUNT ON

DECLARE @CurrentFileDate char(10)

, @OldFileDate char(10)

, @SourceDirFOR varchar(255)

, @FileName varchar(512)

, @DynDelete varchar(512)

, @ProcessName varchar(150)

, @OSVersion decimal(3,1)

, @Error int

SET @ProcessName = 'usp_Admin_Delete_Files_By_Date - [' + @SourceFile + ']'

SET @CurrentFileDate = CONVERT(char(10),getdate(),121)

SET @OldFileDate = CONVERT(char(10),DATEADD(dd,-@DaysToKeep,@CurrentFileDate),121)

SET @SourceDirFOR = 'FOR %I IN ("' + @SourceDir + @SourceFile + '") DO @ECHO %~nxtI'

SET @Error = 0

-- Get Windows OS Version info for proper OSVer statement block exec.

CREATE TABLE #_OSVersion

( [Index] int

, [Name] varchar(255)

, [Internal_Value] varchar(255)

, [Character_Value] varchar(255) )

INSERT INTO #_OSVersion

EXEC master..xp_msver 'WindowsVersion'

SET @OSVersion = (SELECT SUBSTRING([Character_Value],1,3) FROM #_OSVersion)

-- Start temp table population(s).

CREATE TABLE #_File_Details_01

( Ident int IDENTITY(1,1)

, Output varchar(512) )

INSERT INTO #_File_Details_01

EXEC master..xp_cmdshell @SourceDirFOR

CREATE TABLE #_File_Details_02

(Ident int

, [TimeStamp] datetime

, [FileName] varchar(255) )

-- OS Version specifics.

IF @OSVersion = '5.0'

BEGIN -- Exec Windows 2000 version.

INSERT INTO #_File_Details_02

SELECT Ident

, CONVERT(datetime, LEFT(CAST(SUBSTRING([Output],1,8) AS datetime),12)) AS [TimeStamp]

, SUBSTRING([Output],17,255) AS [FileName]

FROM #_File_Details_01

WHERE [Output] IS NOT NULL

ORDER BY Ident

END

IF @OSVersion = '5.2'

BEGIN -- Exec Windows 2003 version.

INSERT INTO #_File_Details_02

SELECT Ident

, CONVERT(char(10), SUBSTRING([Output],1,10), 121) AS [TimeStamp]

, SUBSTRING([Output],21,255) AS [FileName]

FROM #_File_Details_01

WHERE [Output] IS NOT NULL

ORDER BY Ident

END

-- Start delete ops cursor.

DECLARE curDelFile CURSOR

READ_ONLY

FOR

SELECT [FileName]

FROM #_File_Details_02

WHERE [TimeStamp] <= @OldFileDate

OPEN curDelFile

FETCH NEXT FROM curDelFile INTO @FileName

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

SET @DynDelete = 'DEL /Q "' + @SourceDir + @FileName + '"'

EXEC master..xp_cmdshell @DynDelete

END

FETCH NEXT FROM curDelFile INTO @FileName

END

CLOSE curDelFile

DEALLOCATE curDelFile

DROP TABLE #_OSVersion

DROP TABLE #_File_Details_01

DROP TABLE #_File_Details_02

GO