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.
I look forward to your help in this regard.