Rename a network file using xp_cmdshell

  • 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

  • What PRINT shows you?

    _____________
    Code for TallyGenerator

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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!

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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)

  • 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.

  • 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.

  • "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.

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

  • 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.

  • 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.

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

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

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