Trying to rename all files located in \folder , buit not working. No error, seems like logical errr. Print statement looks good

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  April 24, 2015 at 1:12 pm

  #317026

  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.