Post reply

xp_cmdshell file copy / rename

  • August 2, 2016 at 11:13 pm

    #312269

    I need to copy images from FTP server \\ftp\c$\files\*.jpg to a Web server \\Web\D$\files\*.jpg

    using if possible EXEC master..xp_cmdshell

    File names in FTP sever are eg: PersonName1.jpg,Personname2.jpg

    on the destination web sever i want to add 3 letter code eg: XXX the the begining of the file name soe after coping files will be renamed like XXXPersonName1.jpg, XXXPersonname2.jpg

    How do i do that? i have tried below but it cuts off first 3 chars of the new filename eg XXXsonName1.jpg

    DECLARE @cmd VARCHAR(500)

    DECLARE @OutPath varchar(200)

    If @@SERVERNAME = 'Web01'

    SET @OutPath = '\\Web01\d$\WebSites\CD\Images\User\'

    ELSE

    SET @OutPath = '\\Web02\c$\WebSites\CD\Images\User\'

    SET @cmd = 'COPY \\ftp\xxx$\Photos\*.jpg ' + @OutPath + 'XXX' + '*.jpg'

    EXEC master..xp_cmdshell @cmd

  • August 3, 2016 at 12:17 am

    #1893404

    Quick question, have you tried Robocopy?

    😎

  • August 3, 2016 at 3:39 am

    #1893425

    I have used it previously (but not executed from sql server) in this particular case I need to use xp_cmdshell as that is a part of a bigger import script . I thought that it would nicely take care of renaming the photos without looping through the records and executing copy for each individual user in the database.

    Is it possible using xp_cmdshell?

Viewing 3 posts - 1 through 2 (of 2 total)

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