April 24, 2015 at 1:12 pm
SET nocount ON
DECLARE @fileNameNew VARCHAR(1024) -- filename of new error report
DECLARE @fileNameOld VARCHAR(1024) -- filename of old error report
DECLARE @out_fileDate CHAR(1024)
DECLARE @fileDate DATETIME -- used for file name
DECLARE @pathWorking VARCHAR(1024) -- path for text files not dated
DECLARE @path VARCHAR(1024) -- path of final destination
DECLARE @cmd nVARCHAR(1024) -- Final command to rename files
DECLARE @rc INT
DECLARE @DEFAULTS TABLE
(
value VARCHAR(max),
data VARCHAR(max)
)
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)
+
+ CASE WHEN Datepart(hh, Getdate()) < 13 THEN RIGHT
(
Cast(
100+
Datepart(hh, Getdate()) AS CHAR(3)), 2) ELSE
Cast(
Datepart(hh, Getdate())-12 AS CHAR(2)) END +
+ RIGHT(Cast(100+Datepart(mi, Getdate()) AS CHAR(3)
),
2)
--SELECT @PATH = @SOURCEPTH
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 @PATHworking = @PATH + '\Reports\Working'
SET @path = @PATH + '\Folder'
DECLARE curs_oldfiles CURSOR FOR
--SELECT a.output_file_name
--FROM msdb.dbo.sysjobsteps a
--WHERE a.output_file_name <> '[NULL]'
--ORDER BY a.output_file_name
-- Excludes .TZT from filename
SELECT ltrim(rtrim(left(a.output_file_name, len(a.output_file_name) - charindex('.', reverse(a.output_file_name))) ))
FROM msdb.dbo.sysjobsteps a
WHERE a.output_file_name <> '[NULL]'
ORDER BY a.output_file_name
FOR READ only
OPEN curs_oldfiles
FETCH curs_oldfiles INTO @fileNameOld
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileNameNew = @fileNameOld + '_' + @out_fileDate + '.TXT'
PRINT @fileNameNew
SET @cmd = '''rename "'
+ Ltrim(Rtrim(@fileNameOld)) + '.TXT" "'
+ Ltrim(Rtrim(@filenameNew)) + '.TXT"''' + ',no_output'
select @cmd
--EXEC(@cmd)
--exec sp_executesql @cmd
exec master.dbo.xp_cmdshell @cmd , NO_OUTPUT
FETCH curs_oldfiles INTO @fileNameOld
END
CLOSE curs_oldfiles
DEALLOCATE curs_oldfiles
SET nocount OFF
Thanks.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply