master..xp_cmdshell 'DELETE d:\Data\Tax\ETRS\Archive\Oct__1_2003__8_52AM\*.*'

DECLARE @cmd varchar(8000)
SELECT @cmd = 'DELETE d:\Data\Tax\ETRS\Archive\Oct__1_2003__8_52AM\*.*' + CHAR(10)+CHAR(13) + 'Y'
EXEC master..xp_cmdshell @cmd

quote:

---

why are you using xp_cmdshell to do this?
why dont you clean out a directory through the console ?

---

/*
master..xp_cmdshell 'MD d:\Data\Tax\ETRS\Archive\Oct_24_1960__8_52AM'
master..xp_cmdshell 'Dir d:\Data\Tax\ETRS\Archive\*.'
master..xp_cmdshell 'Copy C:\AutoExec.bat d:\Data\Tax\ETRS\Archive\Oct_24_1960__8_52AM'
master..xp_cmdshell 'Dir d:\Data\Tax\ETRS\Archive\Oct_24_1960__8_52AM\*.*'
EXEC isp_Dir_Cleanup 'd:\Data\Tax\ETRS\Archive', 100
master..xp_cmdshell 'Dir d:\Data\Tax\ETRS\Archive\*.'
*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_Dir_Cleanup]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_Dir_Cleanup]
GO
CREATE PROC isp_Dir_Cleanup
	  @Filepath 		varchar(8000)
	, @ArchiveCutoff	int
AS

Declare @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int, @Comp_Code int
	, @Cmd varchar(8000), @Comp_msg varchar(7000), @Dir_Name varchar(8000)

SET NOCOUNT ON

 BEGIN TRAN
	CREATE TABLE #Folder_Parsed (
		Create_Time datetime NULL ,
		File_Size int NULL ,
		File_Name varchar (255) NULL 
	)

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 1
			Select @Error_Type = 50001
  			GOTO isp_Dir_Cleanup_Error
		END
	
	CREATE TABLE #Folder (
		dir_output varchar (255) NULL 
	)

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 2
			Select @Error_Type = 50001
  			GOTO isp_Dir_Cleanup_Error
		END

COMMIT TRAN	
 BEGIN TRAN
	SELECT @Cmd = 'Dir ' + @Filepath + '\*.'
	INSERT INTO #Folder 
	EXEC master..xp_cmdshell @Cmd

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 1
			Select @Error_Type = 50001
  			GOTO isp_Dir_Cleanup_Error
		END

	Delete From #Folder_Parsed

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 2
			Select @Error_Type = 50001
  			GOTO isp_Dir_Cleanup_Error
		END

	DECLARE Dir CURSOR FOR
	SELECT Substring(dir_output,40,(Len(dir_output)-39)) As Dir_Name
	  FROM #Folder
	 WHERE ISDATE(REPLACE(Substring(dir_output,40,11),'_',' '))=1
	   AND DATEDIFF(ww,CONVERT(datetime, REPLACE(Substring(dir_output,40,11),'_',' ')),GetDate()) > @ArchiveCutoff
	   AND Substring(dir_output,25,5) = '<DIR>'

	OPEN DIR

	FETCH NEXT FROM Dir INTO @Dir_Name

	WHILE @@FETCH_STATUS = 0
	
		BEGIN
			SELECT @cmd = 'DEL /Q ' + @FilePath + '\' + @Dir_Name + '\*.*'
			SELECT @cmd
			EXEC master..xp_cmdshell @cmd
			SELECT @cmd = 'RD ' + @FilePath + '\' + @Dir_Name
			SELECT @cmd
			EXEC master..xp_cmdshell @cmd
			FETCH NEXT FROM Dir INTO @Dir_Name
		END

COMMIT TRAN

 BEGIN TRAN

DROP TABLE #Folder
DROP TABLE #Folder_Parsed

COMMIT TRAN

SELECT @Comp_Code = 0

isp_Dir_Cleanup_Exit:

SET NOCOUNT OFF

RETURN @Comp_Code

isp_Dir_Cleanup_Error:
Rollback TRAN

Select @Comp_Code = -1, @Comp_msg = @Error_Message

If @Error_Type = 50001
	BEGIN
		Select @error_message = (Select 'Location: ' + RTRIM(Convert(char(3),@Error_Loc))
					     + '  @@ERROR: ' + RTRIM(Convert(char(6),error))
					     + ' Severity: ' + RTRIM(Convert(char(3),severity))
					     + '  Message: ' + RTRIM(description)
				  	   From master..sysmessages
				 	  Where error = @error_out)
	END
If @Error_Type = 50002
	BEGIN
		Select @Error_Message = 'Location: ' + RTRIM(Convert(char(3),@Error_Loc))
			              + ' Severity:  UserLevel '
				      + ' Message: ' + RTRIM(@Error_Message)
	END

RAISERROR @Error_Type @Error_Message

GOTO isp_Dir_Cleanup_Exit



GO

quote:

---

Originally posted by nr

I think you should test what effect the transactions have on that.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

quote:

---

Originally posted by SamC

Would you post an example using the filescripting object?

---