HOw to change folder name through sql command

Question

Post reply

HOw to change folder name through sql command

halifaxdal

SSCoach

Points: 19741
More actions
July 24, 2008 at 1:13 pm

#196024

I have a need to update folder's info somehow in sql query, I am using [master].[dbo].[xp_cmdshell]
Here is my code, it is not functioning:
DECLARE @OutPut int
DECLARE @Body varchar(3010)
DECLARE @Result varchar(21)
DECLARE @tblTmp TABLE (status VARCHAR(255))
Declare @folder VARCHAR(255)
Declare @path VARCHAR(255)
Declare @folder2 VARCHAR(255)
Declare @path2 VARCHAR(255)
SET @folder = 'C:\Inetpub\wwwroot\Pergamum\Doc\International Banking\Caribbean'
SET @path =SUBSTRING(@folder , 1 , dbo.lastindexof( @folder, '\') )
PRINT @path
SET @Body='cd '+ LTRIM(RTRIM(@path))
PRINT @Body
EXEC @OutPut= [master].[dbo].[xp_cmdshell] @Body
SET @folder = SUBSTRING(@folder , dbo.lastindexof( @folder, '\')+1, LEN(@folder))
PRINT @folder
SET @folder2 = 'Caribbean11'
SET @Body='rename '+ LTRIM(RTRIM(@folder)) +' '+ LTRIM(RTRIM(@folder2))
PRINT @Body
EXEC @OutPut= [master].[dbo].[xp_cmdshell] @Body
IF (@OutPut = 0) BEGIN
INSERT INTO @tblTmp values('Folder renamed!')
--Insert out put values
--INSERT INTO @tblTmp values(@OutPut)
END
ELSE IF (@OutPut = 1) BEGIN
INSERT INTO @tblTmp values('Folder not renamed!')
--Insert out put values
--INSERT INTO @tblTmp values(@OutPut)
END
SELECT * FROM @tblTmp
the function LastIndexOf will work just like other language, no problem with it.
As you can see, I print out the command I will use in [master].[dbo].[xp_cmdshell] , the command itself is correct as follows:
cd C:\Inetpub\wwwroot\Pergamum\Doc\International Banking\
rename Caribbean Caribbean11
Although there is no any problem with the command, it returns: The system cannot find the file specified.
Is it possible to do this task in SQL 2000?
Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Try making the command be:

rename C:\Inetpub\wwwroot\Pergamum\Doc\International Banking\Caribbean Caribbean11

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

halifaxdal SSCoach Points: 19741 More actions · Answer 2

halifaxdal

SSCoach

Points: 19741

July 25, 2008 at 7:27 am

#848370

Thanks for reply.

Your idea is not working.:w00t:

Lowell SSC Guru Points: 323518 More actions · Answer 3

i think this is just a result of spaces in the path/filename, right?

changing this part should work i think:

SET @folder = 'C:\Inetpub\wwwroot\Pergamum\Doc\International Banking\Caribbean'

SET @path =SUBSTRING(@folder , 1 , dbo.lastindexof( @folder, '\') )

PRINT @path

SET @Body='cd "'+ LTRIM(RTRIM(@path)) + '"'

cd "C:\Inetpub\wwwroot\Pergamum\Doc\International Banking\"

rename Caribbean Caribbean11

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql_monkey SSC Eights! Points: 867 More actions · Answer 4

Albeit elementary, Occam's Razor dictates that I begin at what is obvious, because that is often commonly overlooked.

Have you verified that you have access to the folder?

At my shop, file system access and sql access are not always the same. I often find that although I have sa privileges within MSSQL, I have no access to certain folders (even on the C drive of the SQL Server).

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Does this directory exist on the SQL Server which is running the command? 😉

If not, you will need to figure out the address and substitute it for what you're using, something like this:

SET @NewFile = '\\ixnuflawLRS1\D$\Dashboard Reports\SLAReports\' + 'Consolidated' +

REPLACE(CONVERT(VARCHAR(10), GETDATE(), 102), '.', '') + '.xls'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 6

Without doing any testing myself, I think you may be getting the error because the commands to change directory and rename folder are executed in separate batches. I would try creating a batch program then run using osql utility.

halifaxdal SSCoach Points: 19741 More actions · Answer 7

thanks all,

the folder EXISTS in the same server of SQL, no problem when I switch to cmd to do the same work.

GSquared SSC Guru Points: 260824 More actions · Answer 8

I've tested the syntax of the command I suggested, through xp_cmdshell, and it works.

If the folder name has spaces in it, it needs to be in double-quotes.

You also need to be sure the account that xp_cmdshell is running under has rights to the folder.

Beyond that, it should work.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

What do you get when you execute this?

DECLARE @CMD VARCHAR (1000)

SET @CMD = 'DIR C:\Inetpub\wwwroot\Pergamum\Doc\*.*'

IF OBJECT_ID('tempdb..#Output') IS NOT NULL

DROP TABLE #Output

CREATE TABLE #Output (CopyResult VARCHAR(500))

INSERT INTO #Output EXEC master..XP_CMDSHELL @CMD

SELECT * FROM #Output

If you still get "File not found", then change the above to

SET @CMD = 'DIR C:\*.*'

to confirm the context in which you are running the command.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

halifaxdal SSCoach Points: 19741 More actions · Answer 10

DECLARE @CMD VARCHAR (1000)
SET @CMD = 'DIR C:\Inetpub\wwwroot\Pergamum\Doc\*.*'
IF OBJECT_ID('tempdb..#Output') IS NOT NULL
DROP TABLE #Output
CREATE TABLE #Output (CopyResult VARCHAR(500))
INSERT INTO #Output EXEC master..XP_CMDSHELL @CMD
SELECT * FROM #Output

------Chris: I got everything for that folder

I've tested the syntax of the command I suggested, through xp_cmdshell, and it works.
If the folder name has spaces in it, it needs to be in double-quotes.
You also need to be sure the account that xp_cmdshell is running under has rights to the folder.
Beyond that, it should work.

------- GSquared: I already tried your suggestion: rename C:\Inetpub\wwwroot\Pergamum\Doc\International Banking\Caribbean Caribbean11, it just didn't work out.

halifaxdal SSCoach Points: 19741 More actions · Answer 11

I've tested the syntax of the command I suggested, through xp_cmdshell, and it works.
If the folder name has spaces in it, it needs to be in double-quotes.
You also need to be sure the account that xp_cmdshell is running under has rights to the folder.
Beyond that, it should work.

Hi GSquared: your suggestion IS WORKING, when I tested your suggestion, I didn't put double quotes so it failed. Thank you.

And thanks to others too.

GSquared SSC Guru Points: 260824 More actions · Answer 12

halifaxdal (7/25/2008)
I've tested the syntax of the command I suggested, through xp_cmdshell, and it works.
If the folder name has spaces in it, it needs to be in double-quotes.
You also need to be sure the account that xp_cmdshell is running under has rights to the folder.
Beyond that, it should work.
Hi GSquared: your suggestion IS WORKING, when I tested your suggestion, I didn't put double quotes so it failed. Thank you.
And thanks to others too.

You're welcome. It's the simple things that'll trip you up more often than the complex ones. They seem so obvious that they're easy to overlook.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon