There are two types of files; TIF, & TXT stored in two different (Windows Server 2008 and a Networked Attached Storage) servers, shares, and folders without any extensions. Files will be copied over to another storage area. Approximately 92 million files. Any of the two different file types could be in any of the folders. Examples given below:
SQL server database has a view, which has columns for the file type and UNC for each respective file in addition to other columns that are needed for the reports. Using a case statement, I am able to add the extensions to UNC path as shown in the two examples below:
Examples of UNC with extensions added using a case statement
case when filetype = 'PAG' THEN UNC + '.TIF' when filetype = 'CLD' THEN UNC + '.TXT' END
Now I want to be able to add extensions to these files so that when users click on the UNC path in the reports they are able to automatically open the file instead of selecting an application to open it.
What are the different and/or best way to accomplish this task? Can we write some sort of a script, encompassing xp_cmdshell, which basically loops through the records in the sql server view to identify the file type and therefore rename the files in the servers by adding the appropriate extensions.
this will generate the RENAME command. Execute it in xp_cmdshell to rename the file
SELECT cmd = 'REN '
+ UN + ' '
+ RIGHT(UNC, CHARINDEX('\', REVERSE(UNC)) - 1)
+ case when filetype = 'PAG' THEN '.TIF' when filetype = 'CLD' THEN '.TXT' END
FROM youtable
will it loop through the entire 92 million files in the storage and add the extensions to them based on the file type listed in the sql server view/table?
Select @CMD = 'REN' + UNC + ' '
+ RIGHT(UNC, CHARINDEX('', REVERSE(UNC)) - 1)
+ case when Filetype = 'TIF' THEN '.TIF' when Filetype = 'TXT' THEN '.TXT' END
from HPF_Test
GO
exec xp_cmdshell @CMD
When I run the above script I get an error saying
"Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@CMD".
Could anyone please help me identify what is going on?
Select @CMD = 'REN' + UNC + ' '
+ RIGHT(UNC, CHARINDEX('', REVERSE(UNC)) - 1)
+ case when Filetype = 'TIF' THEN '.TIF' when Filetype = 'TXT' THEN '.TXT' END
from HPF_Test;
exec xp_cmdshell @CMD;
However, the output is "Access is denied" & NULL. What could this possibly mean?
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
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:
What can be added to the script to test whether all files were copied or not?
khtan 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.