Hi Lynn,

I am tryoing to duplicate the same file with a different name (basically want to add timestamp to it and want to keep the oputput file for 5 days and then want to delete it. BAsically, do not want to disturb the original file as thats the job output file.

Somehow, the copy command does not work. NO error...But files are not renaming.

Can you please suggest what I am doing wrong here? 🙁

SET nocount ON

DECLARE @path VARCHAR(max)

DECLARE @CMD VARCHAR(4096)

DECLARE @rc INT

DECLARE @DEFAULTS TABLE

(

value VARCHAR(max),

data VARCHAR(max)

)

INSERT INTO @DEFAULTS

EXEC @rc = master.dbo.Xp_instance_regread

N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer',

N'BackupDirectory'

--SELECT *

--FROM @DEFAULTS

SELECT @path = data

FROM @DEFAULTS

WHERE value = 'BackupDirectory'

SELECT @path = @path + '\JOBOUTPUT'

-- SELECT @CMD = 'xp_cmdshell ''del "' + @PATH2 + '\*.TXT*"''' + ',no_output'

--SELECT @CMD

--EXEC(@CMD)

--SET nocount OFF

declare @chkdirectory as varchar(4000)

declare @folder_exists as int

set @chkdirectory = @path

declare @file_results table

(file_exists int,

file_is_a_directory int,

parent_directory_exists int

)

insert into @file_results

(file_exists, file_is_a_directory, parent_directory_exists)

exec master.dbo.xp_fileexist @chkdirectory

select @folder_exists = file_is_a_directory

from @file_results

--script to create directory

if @folder_exists = 0

begin

print 'Directory is not exists, creating new one'

EXECUTE master.dbo.xp_create_subdir @chkdirectory

print @chkdirectory + ' created on' + @@servername

end

else

print 'Directory ('+ @chkdirectory+') already exists'

--SET nocount OFF

Print @chkdirectory

declare @dir VARCHAR(4096)

SET @dir = 'DIR ' + '"' + @chkdirectory + '"'

Print @dir

CREATE TABLE #tempList (Files VARCHAR(500))

INSERT INTO #tempList

EXEC MASTER..XP_CMDSHELL @dir

--delete all directories

DELETE #tempList WHERE Files LIKE '%<dir>%'

--delete all informational messages

DELETE #tempList WHERE Files LIKE ' %'

--delete the null values

DELETE #tempList WHERE Files IS NULL

--get rid of dateinfo

UPDATE #tempList SET files =RIGHT(files,(LEN(files)-20))

--get rid of leading spaces

UPDATE #tempList SET files =LTRIM(files)

--split data into size and filename

SELECT LEFT(files,PATINDEX('% %',files)) AS Size,

RIGHT(files,LEN(files) -PATINDEX('% %',files)) AS FileName

FROM #tempList

drop table #tempList

declare @out_fileDate VARCHAR(1024)

DECLARE @fileNameNew VARCHAR(1024) -- filename of new error report

DECLARE @fileNameOld VARCHAR(1024) -- filename of old error report

SELECT @out_fileDate = Cast(Datepart(yyyy, Getdate()) AS CHAR(4)) +

+ RIGHT(Cast(100+Datepart(mm, Getdate()) AS CHAR(3)

),

2)

+

+ RIGHT(Cast(100+Datepart(dd, Getdate()) AS CHAR(3)

),

2)

+ '_'

+ cast(DATEPART(hour, GETDATE()) as varchar) + '_' + cast(DATEPART(minute, GETDATE()) as varchar)

--Print @out_fileDate

DECLARE curs_oldfiles1 CURSOR FOR

SELECT a.output_file_name

FROM msdb.dbo.sysjobsteps a

WHERE a.output_file_name <> '[NULL]'

ORDER BY a.output_file_name

OPEN curs_oldfiles1

FETCH curs_oldfiles1 INTO @fileNameOld

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileNameNew = @fileNameOld + '_' + @out_fileDate + '.TXT'

set @cmd = '"copy ' + Ltrim(Rtrim(@fileNameOld)) + ' ' + Ltrim(Rtrim(@fileNameNew)) + '"'

--exec (@cmd)

print @cmd

-- EXECUTE master..xp_cmdshell @cmd

FETCH curs_oldfiles1 INTO @fileNameOld

END

CLOSE curs_oldfiles1

DEALLOCATE curs_oldfiles1