Solved

Rename folders based upon folder list in table with xp_cmdshell

Posted on 2004-09-26
8
723 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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
 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

808 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