Solved

Rename folders based upon folder list in table with xp_cmdshell

Posted on 2004-09-26
8
719 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

895 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

11 Experts available now in Live!

Get 1:1 Help Now