xp_cmdshell move not working when dynamic 1

[ssmat]

Hi all,

This seems like it should be easy, but it has me stumped. From the query analyzer I run this command:

exec master..xp_cmdshell 'move "\\servername\frompack\WT010101.txt.txt" "C:\PTArchive\"', no_output

It works great. I want to move this command to a stored procedure but instead of hard coding the paths, I want them to be dynamic. Here's what I have in the sp:


SET @MoveStatement = 'move "' + @FileName + '" "' + @ArchiveDirectory + '", no_output'
print @MoveStatement
exec master..xp_cmdshell @MoveStatement

When I run the sp, I get an error in the output grid "The syntax of the command is incorrect". And in the message that is printed from the print statement is:

move "\\servername\frompack\WT010101.txt" "C:\PTArchive\", no_output

The file is never moved. What looks wrong here? The only thing I can see is that there are not single quotes around the move statement when executing xp_cmdshell in the sp, but how would I do that?

Any help would be greatly appreciated,
ssmat

 

[JamesLean]

The no_output parameter is not part of the command you want to execute. Change to this:

SET @MoveStatement = 'move "' + @FileName + '" "' + @ArchiveDirectory + '"'
print @MoveStatement
exec master..xp_cmdshell @MoveStatement, no_output

--James

 

[ssmat]

Super, thanks!

ssmat