删除早n天的文件 [英] deleting files which are some n days older

问题描述

我该如何编写存储过程来删除文件，这些文件要早n天，而我又要从UI中传递那n天.
n天是可配置的.

谁能帮我.


在sqlserver 2005中.

Hi,

How can i write the stored procedure to delete files which are some n days older and that n days i am passing from UI.
the n days is configurable.

can any one help me.


in sqlserver 2005.

推荐答案

Sql Jobs可以帮助您

通过以下链接

http://www.databasedesign-resource.com/sql-server-jobs.html [ ^ ]

Sql Jobs can help you

go through the below link

http://www.databasedesign-resource.com/sql-server-jobs.html[^]

以下是存储过程，用于从文件共享中删除早于n天的文件.


如果存在(从dbo.sysobjects中选择[名称]，[名称] =``usp_Admin_Delete_Files_By_Date''和TYPE ="P")
删除程序dbo.usp_Admin_Delete_Files_By_Date
GO
创建过程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
/***************************************************** *****************************
**
**名称:usp_Admin_Delete_Files_By_Date.sql
**
**说明:根据路径&删除X天之前的文件.扩展名.
**
**根据xp_msver的输出，我们将执行
** Windows 2000或Windows 2003特定的INSERT INTO#_File_Details_02
**操作，因为在
之间的FOR输出之间存在很小的差异 ** Windows 2000和2003(操作系统版本).
**
**返回值:0-成功
** -1-错误
**
**作者:G. Rayburn
**
**日期:2007年3月26日
**
**取决于:通过SQLAgent帐户xp_cmdshell对@SourceDir的访问.
**
****************************************************** *****************************
**修改历史记录
****************************************************** *****************************
**
**初始创作:2007年3月26日G. Rayburn
**
****************************************************** *****************************
**
****************************************************** ********************************/
设置NOCOUNT ON
声明@CurrentFileDate char(10)
，@OldFileDate char(10)
，@SourceDirFOR varchar(255)
，@FileName varchar(512)
，@DynDelete varchar(512)
，@ProcessName varchar(150)
，@OSVersion十进制(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

-获取正确的OSVer语句块执行程序的Windows操作系统版本信息.
创建表#_OSVersion
([索引] int
，[名称] varchar(255)
，[Internal_Value] varchar(255)
，[Character_Value] varchar(255))
插入#_OSVersion
EXEC master..xp_msver``WindowsVersion''
SET @OSVersion =(从#_OSVersion选择SUBSTRING([[Character_Value]，1,3))

-开始临时表填充.
创建表#_File_Details_01
(Ident int IDENTITY(1,1)
，输出varchar(512))
插入到#_File_Details_01
EXEC主站..xp_cmdshell @SourceDirFOR
创建表#_File_Details_02
(Ident int
，[TimeStamp] datetime
，[FileName] varchar(255))

-操作系统版本的详细信息.
如果@OSVersion =``5.0''
开始-Exec Windows 2000版本.
插入到#_File_Details_02
SELECT Ident
，CONVERT(datetime，LEFT(CAST(SUBSTRING([Output]，1,8)AS datetime)，12))AS [TimeStamp]
，SUBSTRING([输出]，17,255)AS [文件名]
来自#_File_Details_01
[输出]在哪里不为空
按标识排序
END
IF @OSVersion =''5.2''
开始-Exec Windows 2003版本.
插入到#_File_Details_02
SELECT Ident
，CONVERT(char(10)，SUBSTRING([Output]，1,10)，121)AS [TimeStamp]
，SUBSTRING([输出]，21,255)AS [文件名]
来自#_File_Details_01

[输出]在哪里不为空
按标识排序
END

-开始删除操作光标.
DECLARE curDelFile光标
READ_ONLY
FOR
SELECT [文件名]
来自#_File_Details_02
在[TimeStamp]< = @OldFileDate
打开curDelFile
从curDelFile获取下一个文件到@FileName
WHILE(@@ fetch_status<> -1)
开始
IF(@@ fetch_status<> -2)
开始
SET @DynDelete =''DEL/Q''+ @SourceDir + @FileName +''"''
EXEC主站..xp_cmdshell @DynDelete
END
从curDelFile获取下一个文件到@FileName
END
关闭curDelFile
释放curDelFile
删除表#_OSVersion
删除表#_File_Details_01
删除表#_File_Details_02
开始

Below is stored procedure 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

这篇关于删除早n天的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！