Move & Rename file

alter Procedure testLoad
(
@FileNamevarchar(50),
@Logonvarchar(20),
)
As
SET NOCOUNT ON

DECLARE@File_Existsint,
@textfilechar(150),
@BusArea char(2),
@PayId char(3),
@Login varchar(20),
@CycleNochar(15),
@FHBusArea char(2),
@FHPayId char(3),
@FHCycleNochar(15),
@TrBatchPoNochar(10),
@rename varchar(255),
@file_extn datetime

SET @textfile='EevelopmentSIRS'+@FileName+'.txt'

EXEC Master..xp_fileexist @textfile,@File_Exists OUTPUT

EXEC Master..xp_cmdshell 'move @textfile EevelopmentSIRSArchive',NO_OUTPUT

SET @file_extn = (LEFT(GETDATE(), 12) )
SELECT @rename ='ren "EevelopmentSIRSArchive'+@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
----------



Thanks!
"He laughs best who laughs last"

Hi Reddy

SELECT @rename ='ren "EevelopmentSIRSArchive'+@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

Waqar

________________________________________________
~* Opinions are like a$$holes, everyone got one. *~

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

Thanks!
"He laughs best who laughs last"

Reddy,

EXEC Master..xp_cmdshell 'move @textfile EevelopmentSIRSArchive',NO_OUTPUT
you need to change to

'move ' + @textfile + ' EevelopmentSIRSArchive'

so you can do

declare @sql varchar(500)
set @sql='move ' + @textfile + ' EevelopmentSIRSArchive'

EXEC Master..xp_cmdshell @sql

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

Waqar.

________________________________________________
~* Opinions are like a$$holes, everyone got one. *~

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 "EevelopmentSIRSArchive'+@FileName+'.txt" '+ @file_extn
EXEC master..xp_cmdshell @rename

Thanks!
"He laughs best who laughs last"

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.

--EDIT--
or you can also perform

SELECT @rename ='ren "EevelopmentSIRSArchive'+@FileName+'.txt" '+ LTRIM(RTRIM(@file_extn))
--EDIT--

________________________________________________
Be great in act, as you have been in thought.

no gud luck, still can not rename it.

Thanks!
"He laughs best who laughs last"

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

How come that is related to this issue?

Thanks!
"He laughs best who laughs last"

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

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

Thanks!
"He laughs best who laughs last"

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?

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


Thanks!
"He laughs best who laughs last"

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.

thanks, it working now.

Thanks!
"He laughs best who laughs last"