Rename folders based upon folder list in table with xp_cmdshell

Is there as way usign something like xp_cmdshell where I can take a list of folders in my windoes directory and if they are located within a select statement that I am pulling from a table that contains the folder names, rename the folder?

I have a SQL table with a list of folder names - the folder are named as such -
512345
554234

Each of the folder names is 6 total characters in length and all are prefixed with a "5"  - what I need to do is to rename all the folders in a directory, based upon athe list pulling from the select statement and simply rename the coressponding folders by stripping the "5" from teh front - I am severly desperta en need some help - so please, any help would truly save my day!!!!!
tbaseflugAsked:
Who is Participating?
 
mcmonapConnect With a Mentor Commented:
Hi tbaseflug,

I had this the other way around, below should do what you want.  A cursir is created containing a the list of foldernames in your table, against each of these your run the command "rename c:\test\5foldername foldername" (I have a directory called test which holds the folders I am renaming).  If the folder from the table doesn't exist nothing is changed in the file system if it does then it is renamed to the entry in the table.

--begin code
DECLARE @FolderName VARCHAR(255)
DECLARE cFolders CURSOR  FAST_FORWARD
FOR
SELECT DISTINCT FolderName FROM yourtable

OPEN cFolders
FETCH NEXT FROM cFolders INTO @FolderName
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @folderName = 'rename c:\test\5' + @folderName + ' ' + @folderName
     exec xp_cmdshell @folderName, no_output
     FETCH NEXT FROM cFolders INTO @FolderName
END
--end code
0
 
mcmonapCommented:
Hi tbaseflug ,

I think this will do what you want

--start code
DECLARE @FolderName VARCHAR(255)
DECLARE cFolders CURSOR  FAST_FORWARD
FOR
SELECT DISTINCT FolderName FROM yourtable

OPEN cFolders
FETCH NEXT FROM cFolders INTO @FolderName
WHILE @@FETCH_STATUS = 0
BEGIN
      SET @folderName = 'rename c:\test\' + @folderName + ' ' + SUBSTRING(@folderName,2,255)
      exec xp_cmdshell @folderName, no_output
      FETCH NEXT FROM cFolders INTO @FolderName
END
--end code
0
 
tbaseflugAuthor Commented:
mcmonap -

Can you explain to how this works in simple terms
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tbaseflugAuthor Commented:
I am not certain I understand 100% -

I have a list of folders - some will need to be updated - some  till not - essentially, each ofthe folders that will need to updated are listed within a SQL table -

The folder are named like: 512345

In the table they are named like: 12345

What I need to do is to take a list from the table (12345) - locate the corresponding folder (which would be the same as from table - 12345 only with a "5" prefix = 512345 - and when there is a match such as this, rename the folder by removing the "5" prefix so that the folder becomes = 12345
0
 
tbaseflugAuthor Commented:
msmonap -

Thanks - looks like it will work - I am getting an access denied messge (removed no_output) - am not certain what the issue is as I am set as a local admin on the server - any ideas?
0
 
mcmonapCommented:
Hi tbaseflug,

you need to be logged into sql server with a sys_admin account in order to use the xp_cmdshell extended stored procedure - can you login as sa?
0
 
tbaseflugAuthor Commented:
Let me give it a try -

Also, could you tell me to how I can get, in a select everythign from a string, such as below, with the exception of the "81639\"

\DOCS\NOrleans\64419\81639\
0
 
mcmonapCommented:
Hi tbaseflug,

It depends on whether the string length is always the same length before the bit you want to omit, if it is then it should be simply:
SELECT LEFT('\DOCS\NOrleans\64419\81639\',21)

If the strings are variable length and you want omit the final directory level try something like below (assuming you are selecting from a table and your column is called col1)

SELECT LEFT(col1,LEN(col1) - CHARINDEX('\',SUBSTRING(REVERSE(col1),2,1024)))
FROM yourtable
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.