move & rename file from SP

• Mike Levan

  SSChampion

  Points: 14201

  More actions

  August 11, 2006 at 8:46 am

  #119315

  alter Procedure testLoad

  (

  @FileName varchar(50),

  @Logon varchar(20),

  )

  As

  SET NOCOUNT ON

  DECLARE @File_Exists int,

  @textfile char(150),

  @BusArea char(2),

  @PayId char(3),

  @login varchar(20),

  @CycleNo char(15),

  @FHBusArea char(2),

  @FHPayId char(3),

  @FHCycleNo char(15),

  @TrBatchPoNo char(10),

  @rename varchar(255),

  @file_extn datetime

  SET @textfile='E:\Development\SIRS\'+@FileName+'.txt'

  EXEC Master..xp_fileexist @textfile,@File_Exists OUTPUT

  EXEC Master..xp_cmdshell 'move @textfile E:\Development\SIRS\Archive',NO_OUTPUT

  SET @file_extn = (LEFT(GETDATE(), 12) )

  SELECT @rename ='ren "E:\Development\SIRS\Archive\'+@FileName+'.txt" '+ '@file_extn

  EXEC master..xp_cmdshell @rename

  GO

  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.

  NULL

  ----------

• Bhushan

  Default port

  Points: 1427

  More actions

  August 11, 2006 at 9:41 am

  #654602

  alter Procedure testLoad

  (

  @FileName varchar(50),

  @Logon varchar(20),

  )

  As

  SET NOCOUNT ON

  DECLARE @File_Exists int,

  @textfile char(150), --> Should be varchar

  @BusArea char(2),

  @PayId char(3),

  @login varchar(20),

  @CycleNo char(15),

  @FHBusArea char(2),

  @FHPayId char(3),

  @FHCycleNo char(15),

  @TrBatchPoNo char(10),

  @rename varchar(255),

  --@file_extn datetime -- Should be varchar

  @file_extn varchar(12)

  SET @textfile='E:\Development\SIRS\'+@FileName+'.txt'

  EXEC Master..xp_fileexist @textfile,@File_Exists OUTPUT

  --EXEC Master..xp_cmdshell 'move @textfile E:\Development\SIRS\Archive',NO_OUTPUT

  EXEC Master..xp_cmdshell 'move '+ @textfile+ ' E:\Development\SIRS\Archive',NO_OUTPUT

  --SET @file_extn = (LEFT(GETDATE(), 12) )

  SET @file_extn = REPLACE(RTRIM((LEFT(GETDATE(), 12) )),' ','')

  SELECT @rename ='ren "E:\Development\SIRS\Archive\'+@FileName+'.txt" '+ '@file_extn

  EXEC master..xp_cmdshell @rename

  GO

• Mike Levan

  SSChampion

  Points: 14201

  More actions

  August 11, 2006 at 9:50 am

  #654606

  still am geting error at ' + '

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  August 11, 2006 at 10:06 am

  #654611

  >>still am geting error at ' + '

  -- Execute "Dir *.txt"

  exec master..xp_cmdshell 'Dir' + '*.txt'

  Server: Msg 170, Level 15, State 1, Line 2

  Line 2: Incorrect syntax near '+'.

  xp_cmdshell does not support inline string concatenation. Concatenate the required string parts together into a varchar variable.

  Declare @CommandLine varchar(500)

  Select @CommandLine = 'Dir ' + ' *.txt'

  exec master..xp_cmdshell @CommandLine

   

• Jeff Moden

  SSC Guru

  Points: 1004366

  More actions

  August 13, 2006 at 8:32 pm

  #654728

  PW is correct AND....

  You will probably have to use the machine name and path instead of drive name and path.  AND, your server must startup as something that has privs on the drive and directory.  A bit of a moot point if the "E" drive belongs to the server but what the heck are you doing with loading server drives with text files, anyway?

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