SQL Server Performance

Move & Rename file

Discussion in 'General Developer Questions' started by Reddy, Aug 11, 2006.

  1. Reddy New Member

    alter Procedure testLoad

    @BusArea char(2),
    @PayId char(3),
    @Login varchar(20),
    @FHBusArea char(2),
    @FHPayId char(3),
    @rename varchar(255),
    @file_extn datetime

    SET @textfile='E:DevelopmentSIRS'+@FileName+'.txt'

    EXEC Master..xp_fileexist @textfile,@File_Exists OUTPUT

    EXEC Master..xp_cmdshell 'move @textfile E:DevelopmentSIRSArchive',NO_OUTPUT

    SET @file_extn = (LEFT(GETDATE(), 12) )
    SELECT @rename ='ren "E:DevelopmentSIRSArchive'+@FileName+'.txt" '+ '@file_extn
    EXEC master..xp_cmdshell @rename


    I am getting the follwing mesg and its not serving the purpose of moveing a file and renaming it on date extension.
    The system cannot find the file specified.

    "He laughs best who laughs last"

  2. Luis Martin Moderator

    Did you try replacing " by '?

    Luis Martin

    All in Love is Fair
    Stevie Wonder

    All postings are provided “AS IS” with no warranties for accuracy.

  3. satya Moderator

    Are you trying to run this as a SQLagent schedule?
    If so then ensure the SQLagent has required privileges on the specified path or check whether path exists as per the specification.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  4. waqar Member

    Hi Reddy

    SELECT @rename ='ren "E:DevelopmentSIRSArchive'+@FileName+'.txt" '+ '@file_extn

    Why this comma quote is here?

    What you can do is to print your command and try to execute those command manually at shell and see if it works


    ~* Opinions are like a$$holes, everyone got one. *~
  5. Reddy New Member

    That is fine but first am unable to move the file, it says file cannot found.

    "He laughs best who laughs last"

  6. waqar Member


    EXEC Master..xp_cmdshell 'move @textfile E:DevelopmentSIRSArchive',NO_OUTPUT
    you need to change to

    'move ' + @textfile + ' E:DevelopmentSIRSArchive'

    so you can do

    declare @sql varchar(500)
    set @sql='move ' + @textfile + ' E:DevelopmentSIRSArchive'

    EXEC Master..xp_cmdshell @sql

    It is always a good practise to DEBUG when you get problem, you can debug by PRINTING command which is giving problem for your case you could debug as

    EXEC Master..xp_cmdshell @sql -> PRINT @sql


    ~* Opinions are like a$$holes, everyone got one. *~
  7. Reddy New Member

    From the below code am getting error as incorrect command syntax BUT when I change the variable lenght to 12 instead of 255 its wroking, what might be the reason. I want the length to be more than 12 so that I get date and time completely.

    DECLARE @file_extn varchar(255)

    SET @file_extn =@FileName+Convert(varchar,getdate(),21)

    SELECT @rename ='ren "E:DevelopmentSIRSArchive'+@FileName+'.txt" '+ @file_extn
    EXEC master..xp_cmdshell @rename

    "He laughs best who laughs last"

  8. waqar Member

    Hi Reddy,

    Seems variable @rename is not big enough to hold your entire query.

    Before executing following query EXEC master..xp_cmdshell @rename i will suggest to perform PRINT @rename to see if your request is truncated.

    or you can also perform

    SELECT @rename ='ren "E:DevelopmentSIRSArchive'+@FileName+'.txt" '+ LTRIM(RTRIM(@file_extn))

    Be great in act, as you have been in thought.
  9. Reddy New Member

    no gud luck, still can not rename it.

    "He laughs best who laughs last"

  10. Adriaan New Member

    Check under which account the SQL Server service is running, then check which privileges this account has on the E: drive (which must be the E: drive as defined in the profile of that account).
  11. Reddy New Member

    How come that is related to this issue?

    "He laughs best who laughs last"

  12. Adriaan New Member

    Because the service must have sufficient privileges on a drive in order to manipulate files - like changing the file name.
  13. Reddy New Member

    My problem is not manipulating the file, am able to do that but NOT as according to my requirement.

    "He laughs best who laughs last"

  14. Adriaan New Member

    Sorry for the confusion ...

    Did you test by PRINTing the command line, as suggested by others, to see if there was anything funny going on?
  15. Reddy New Member

    What I need to do to get time in the following statement

    REPLACE(RTRIM((LEFT(GETDATE(),12) )),' ','')

    when I change the number to 20, I get the following error
    A duplicate file name exists, or the file

    "He laughs best who laughs last"

  16. Adriaan New Member

    You need to avoid both the time and date delimiters. For the date, you can use format switch 12 or 112, but there is no option for time. You can get the time with format switch 108, but you have to suppress the colon (<img src='/community/emoticons/emotion-1.gif' alt=':)' /> because it is not allowed on file names:<br /><br />CONVERT(VARCHAR, GETDATE(),112) + REPLACE(CONVERT(VARCHAR, GETDATE(), 10<img src='/community/emoticons/emotion-11.gif' alt='8)' />, ':', '')<br /><br />You could add a hyphen between date and time.
  17. Reddy New Member

    thanks, it working now.

    "He laughs best who laughs last"

Share This Page