2

I have been struggling with the following error for 2 days now ERROR: UNC paths (\\machine\share) are not supported. and have finally come across something that got plenty of up-votes here.

The poster mentions using pushd and popd to map to a free drive letter. But I have absolutely no Idea how to do this. and quite frankly, i'm afraid I mess something up :(

How would I implement this pushd & popd into my SQL code:

    DECLARE @N AS VARCHAR(2) -- days for retention
    DECLARE @path AS VARCHAR(128) -- the path for deletion
    DECLARE @cmd AS VARCHAR(512) -- the actual command
  
    SET @N = '7' -- change the days here (Deleting files which are older that @N days)
    SET @path = '\\TOMANYMS\c$\JohnDoe\91\TEST\serverapps\export' -- Path of the files you would like to check
    SET @cmd = 'FORFILES /P "' + @path + '" /S /M export_status.* /D -' + @N + ' /C "cmd /c del @PATH"' --Deletes on the export_status files older than 7 days.

    EXEC master.dbo.xp_cmdshell @cmd

I assume I would need to put it in the line where I am setting @cmd.

Any help would be great,

Thanks so much

Community
  • 1
  • 1
Fizor
  • 1,480
  • 1
  • 16
  • 31
  • If I understand correctly you'd like to search a path and its subfolders for a certain file mask and delete if the file is older than 7 days? I could suggest a number of other ways to do this from SQL which don't require `FORFILES ` if you wish. – Nick.Mc Apr 08 '15 at 10:21
  • @Nick.McDermaid - That would be absolutely great! I thought this was the only way to really go about it :) – Fizor Apr 08 '15 at 10:22
  • For example this page shows the ROBOCOPY method of doing this and I'm guesing ROBOCOPY doesn't mind UNC's... any reason you don't want to use that method? http://ss64.com/nt/syntax-delolder.html – Nick.Mc Apr 08 '15 at 10:23
  • You could also write a T-SQL cursor to loop through the files and delete as required – Nick.Mc Apr 08 '15 at 10:23

1 Answers1

1

You can chain the DOS command using the & operator like this:

set @cmd = 'pushd '+ @path+  ' & FORFILES /P \ /S /M export_status.* /D -' + @N + ' /C "cmd /c del @PATH"'+' & popd'
Alex
  • 21,273
  • 10
  • 61
  • 73
  • Thanks Jaco, I tried this out and still get some errors. I have just decided to go the `SSIS` route. Thank you for your help. – Fizor Apr 08 '15 at 13:19