Rename a network file using xp_cmdshell

Question

Post reply

Rename a network file using xp_cmdshell

joemai

Hall of Fame

Points: 3496
More actions
November 5, 2015 at 4:16 pm

#322129

Please help. I'd like to rename a network file by adding date to the file name. My script likes this:
declare @date varchar (10)
set @date = (select replace(convert(varchar(10),getdate(),101),'/',''))
declare @CMD NVARCHAR(4000)
SET @CMD='" ren \\pashare\shared\dhsr data\Adolescent Health\IMG\PHI_Refresh\export_file_IMG.xlsx" export_File_IMG_' +@date+'.xlsx'
PRINT @CMD
EXEC master..xp_cmdshell @CMD
It gave me the error 'The syntax of the command is incorrect.'
What's wrong with mine? My sql service acct does have access to that network file.
Thanks,
Minh Vu

Viewing 11 posts - 1 through 10 (of 10 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

What PRINT shows you?

_____________
Code for TallyGenerator

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

SET @CMD='ren "\\pashare\shared\dhsr data\Adolescent Health\IMG\PHI_Refresh\export_file_IMG.xlsx" "export_File_IMG_' +@date+'.xlsx"'

(Assuming date is a char data type and thus doesn't need CAST to char).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lowell SSC Guru Points: 323518 More actions · Answer 3

i seem to remember that xp_cmdshell is limited to a single set of double quotes, right?

so "\path with spaces\oldname" "\path with spacesewname" might return an error?

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!

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

I don't think so. I use that style all the time to rename files (yes, I should use Windows directly, but I'm used to working from within SQL 🙂 ).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Lowell (11/6/2015)
i seem to remember that xp_cmdshell is limited to a single set of double quotes, right?
so "\path with spaces\oldname" "\path with spacesewname" might return an error?

No. Each path can have it's own set of double-quotes for this type of DOS command.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

Jeff Moden (11/22/2015)
Lowell (11/6/2015)
i seem to remember that xp_cmdshell is limited to a single set of double quotes, right?
so "\path with spaces\oldname" "\path with spacesewname" might return an error?
No. Each path can have it's own set of double-quotes for this type of DOS command.

It absolutely can. The problem is the original post was that the double quotes were before the ren command, so the full path/filename wasn't inclosed in double quotes.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Ed Wagner (11/22/2015)
Jeff Moden (11/22/2015)
Lowell (11/6/2015)
i seem to remember that xp_cmdshell is limited to a single set of double quotes, right?
so "\path with spaces\oldname" "\path with spacesewname" might return an error?
No. Each path can have it's own set of double-quotes for this type of DOS command.
It absolutely can. The problem is the original post was that the double quotes were before the ren command, so the full path/filename wasn't inclosed in double quotes.

Oops. Scott already posted this exact move of the double quotes.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

MMartin1 One Orange Chip Points: 27795 More actions · Answer 8

"export_File_IMG_'

I dont work with this a whole lot so I was wondering if the single quote closing the double quote is valid, or if it is just covering the date string ...if an extra escape single quote is not needed. Thanks.

----------------------------------------------------

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

MMartin1 (11/23/2015)
"export_File_IMG_'
I dont work with this a whole lot so I was wondering if the single quote closing the double quote is valid, or if it is just covering the date string ...if an extra escape single quote is not needed. Thanks.

The single quote isn't closing the double quote. The single quotes are for the SQL string. The double quotes are contained within the command you want to execute to allow for spaces in your path and filenames. The double quotes close each other in the DOS command.

To get a picture of what command you'll be executing, look at the results of your PRINT statement. You'll see that the single quotes aren't even there. The @date variable is concatenated with the rest of your string to produce the DOS command you want.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

MMartin1 One Orange Chip Points: 27795 More actions · Answer 10

Thanks Ed, I had to look at it for a minute to figure it out. It looks like that single quote is closing the first one in front of ren.. and the next one opens the .xlsx portion and it then has its own closing single quote at the end.

----------------------------------------------------