Friday, November 6, 2009

Stored Procedure to delete files from a folder

When your only way of managing SQL Server is through Enterprise Manager or Query Analyzer, you will have to do a lot of tasks through command shell. If you find yourself routinely conduct some activities using xp_cmdshell, it is a good idea to wrap those routines into a stored procedure. This example takes an input parameter for directory name and delete all files within that directory. The directory name can be UNC path. I put some comments within this procedure so it should be easy to follow.

CREATE proc usp_DeleteFileInFolder @FolderName varchar(150) as
--Created by Haidong Ji
SET NOCOUNT ON

declare @DOSCommand varchar(150)
--Check whether the user supply \ in the directory name
if not (right(@FolderName, 1) = '\')
set @FolderName = @FolderName + '\'
--Delete all files within this folder. Note: del *.* only deletes files,
--not folders. Q is the quite switch so DOS will not ask for confirmation
set @DOSCommand = 'del /Q ' + '"' + @FolderName + '*.*' + '"'
print @DOSCommand
exec master..xp_cmdshell @DOSCommand
GO



For Delete a particular file : xp_cmdshell 'del c:\testfile.txt'
For More Info : http://www.sqlservercentral.com/articles/Administration/usingxp_cmdshell/1250/

No comments:

Post a Comment