SQL Server DeleteFolder Job ran successfully but it doesn't deleted the folder
0
votes
1
answer
445
views
I have a job to delete past 15 days folder in SQL Server. The job run successfully and delete the folder in local SQL Server.
But it runs successfully and does not delete the folder in another SQL Server which is accessed by remote desktop connection.
Please suggest how to solve it. Is it permission problem?
The following is the code I use
{
--Script to enable the XP_CMDSHELL
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @cmd VARCHAR(8000)
DECLARE @folderName VARCHAR(256) -- filename for backup
SET @folderName = + GETDATE()-15 --(CONVERT(varchar(10), GETDATE()-15, 'yyyy_mm_dd')) -- 15 days back date folder name
SET @folderName = CONVERT(varchar(4),YEAR(@folderName)) + '_' + Right('00' + CONVERT(varchar(2),MONTH(@folderName)),2) + '_' + Right('00' + CONVERT(varchar(2),DAY(@folderName)),2)
SET @path = 'D:\FTP-Snapshots\' + @folderName -- Folder path
SET @cmd = 'RD ' + @path + ' /S /Q' -- Delete Files and the Folder IF all files deletions were successful.
EXEC master..xp_cmdshell @cmd
}
Asked by San
(11 rep)
Jul 30, 2015, 08:02 AM
Last activity: Nov 17, 2020, 08:07 PM
Last activity: Nov 17, 2020, 08:07 PM