Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Rename folders based upon folder list in table with xp_cmdshell

Posted on 2004-09-26
8
Medium Priority
?
773 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 15

Accepted Solution

by:
mcmonap earned 2000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

670 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