move & rename file from SP

Question

Post reply

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

Bhushan Default port Points: 1427 More actions · Answer 1

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 · Answer 2

Mike Levan

SSChampion

Points: 14201

August 11, 2006 at 9:50 am

#654606

still am geting error at ' + '

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

>>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 · Answer 4

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)