Renaming Files Using XP_CMDSHELL?

Question

The output ScreenshotI have a script, which loops through the SQL Server database table to determine the file type and subsequently rename the files on the network share to add the appropriate extension. Script given below:

DECLARE @UNC AS varchar(255), @Filetype AS varchar(255), @CMD AS VARCHAR(255)


declare @HPF_Test as cursor;

SET @HPF_Test = CURSOR FOR


    select UNC, Filetype
    from HPF_Test;


OPEN @HPF_Test;
    Fetch NEXT from @HPF_Test into @UNC, @Filetype;
    While @@fetch_status=0
        begin 
            SELECT @CMD = 'REN ' + @UNC + ' '
 + RIGHT(@UNC, CHARINDEX('\', REVERSE(@UNC)) - 1)
 + case when @Filetype = 'TIF' THEN '.TIF' when @Filetype = 'TXT' THEN '.TXT' when @Filetype = 'XML' THEN '.XML' END 
 FROM HPF_Test 
           exec master..xp_cmdshell @CMD
            Fetch next from @HPF_Test into @UNC, @Filetype
        end

close @HPF_Test;
deallocate @HPF_Test;

Since there are approximately 92 million images that will need to be renamed it will take time for the whole process to run and complete. Therefore, I have a few questions that are listed below:

1) What can be added to the script to test whether all files were copied or not?

2) Someone suggested that we run the script in batches. How can I break it into batches?

Please help me in this regard. I am a novice in this regard.

Thanks!

Updates:

I have three files that I am testing this code on. So, when I run the above code I get three outputs, that are all NULL. However, when I try to capture the results in a table I get two records 1) The system cannot find the file specified & 2)NULL

I am using adding the script below to the original script to capture the results in a table.

CREATE TABLE #TEMP_test3(t nvarchar(255))

 insert  #TEMP_test3(t)

exec master..xp_cmdshell @CMD

 select * from

 #TEMP_test3

Could anyone please help me in this regard or at least tell me what am I doing wrong here?

Thanks!!!

the [`exec master..xp_cmdshell`](https://msdn.microsoft.com/en-us/library/ms175046.aspx?f=255&MSPPError=-2147217396) returns the result of the operation to the `nvarchar(255)` column. You can check what is returned and write it to the table. E.g. if you try to rename a file that does not exists the REN command displays: `The system cannot find the file specified.` — cha, Dec 16 '15 at 01:05
What if the command fails for some reason at some point, let say for instance after renaming 42 million files, then how will I find out where it failed? And subsequently how can I re-run the command re-naming files from that point onwards? — iTee, Dec 16 '15 at 21:34
I suggest you add a column to your HPF_Test table called `result`. Just write the result of the xp_cmdshell command into this column. You can then run query against this column to see which one failed — cha, Dec 16 '15 at 22:00
Could you please tell me how to do that? As in what and where do I need to add to the existing code? — iTee, Dec 16 '15 at 22:53

score 0 · Answer 1 · answered Dec 17 '15 at 00:49

This could take forever due to the constant context switch if you'll be using a separate call to command shell for every file to be renamed. It would be much faster to start from a (python/vb/shell/etc) script and work the reverse angle instead.

The script would read the list of files from the database, attempt to rename them, keep track of success or failure, then write back the results to the database. I'm guessing the process would complete in 1/100th of the time.

Cahit, you're right. We just did the math and it seems like it will take approx. 60 days to rename all the 92 million files. — iTee, Dec 18 '15 at 00:04

Renaming Files Using XP_CMDSHELL?

1 Answers1