Moving and renaming files using xp_cmdshell

I need to move and rename a file but for reasons I'm not going into can't use SSIS or DTS. I'm trying to use xp_cmdshell and getting the message "The syntax of the command is incorrect". I'm using examples I've found on line and am confused as to what I've done wrong. The rename should add the date onto the filename, for example EandWTest.txt should be moved to an archive folder and renamed EandWTest_0110523_16_07.txt.

This is the code I've found and am trying to fit my needs, I'm not actually using the variables to stipulate the filename and path but will do that when I've got the basic code working:

@TodayDate as varchar(40),
@TodayHour as varchar(40),
@TodayMinu  as varchar(40),
@NewFileName as varchar(100),
@cmdstr as varchar(128),
@cmd VARCHAR(255),
@sFileName AS VARCHAR(255),
@sPath AS VARCHAR(255)

SET @sFileName = 'EandWTest.txt'
SET @sPath = 'C:\Auto Import\Auto Import\'

SELECT @TodayDate = CONVERT(varchar(10), GETDATE(), 112)
SELECT @NewFileName = @sFileName + '_' + @TodayDate + '_' + @TodayHour + '_' + @TodayMinu + '.txt'
print @NewFileName
set @cmdstr='MOVE /Y C:\Auto Import\Auto Import\EandWTest.txt C:\Auto Import\Auto Import\Archive\'  + @NewFileName  
print @cmdstr
EXEC master..xp_cmdshell @cmdstr
Many thanks.
more ▼

asked May 23, 2011 at 07:55 AM in Default

Mrs_Fatherjack gravatar image

4.7k 60 62 67

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You are missing quotes around the path. You have spaces in the folder names and windows command line doesn't like that. Just put double quotes around the filenames to fix that.

Also the value of @NewFileName is a bit messed up, just running the print command returned:

MOVE /Y C:\Auto Import\Auto Import\EandWTest.txt C:\Auto Import\Auto Import\Archive\EandWTest.txt_20110523_17_13.txt

So the .txt part at the end is not quite right.


So you want the command to end up looking like this:

MOVE /Y "C:\Auto Import\Auto Import\EandWTest.txt" "C:\Auto Import\Auto Import\Archive\EAndWTest.200110523_17_13.txt"
more ▼

answered May 23, 2011 at 07:59 AM

WilliamD gravatar image

25.9k 17 19 41

@WilliamD Awesome, that's fantastic many thanks.
May 23, 2011 at 08:06 AM Mrs_Fatherjack
You're welcome.
May 23, 2011 at 08:08 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 23, 2011 at 07:55 AM

Seen: 5755 times

Last Updated: May 23, 2011 at 07:55 AM