Solved

Rename folders based upon folder list in table with xp_cmdshell

Posted on 2004-09-26
8
713 Views
Last Modified: 2012-05-05
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!!!!!
0
Comment
Question by:tbaseflug
  • 4
  • 4
8 Comments
 
LVL 15

Expert Comment

by:mcmonap
ID: 12155550
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
 

Author Comment

by:tbaseflug
ID: 12155627
mcmonap -

Can you explain to how this works in simple terms
0
 

Author Comment

by:tbaseflug
ID: 12155654
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
 
LVL 15

Accepted Solution

by:
mcmonap earned 500 total points
ID: 12155778
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:tbaseflug
ID: 12155826
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
 
LVL 15

Expert Comment

by:mcmonap
ID: 12155876
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
 

Author Comment

by:tbaseflug
ID: 12155941
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
 
LVL 15

Expert Comment

by:mcmonap
ID: 12156013
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now