How to copy a file with timestamp using xp_cmdshell,

[kavijaya]

filename to be copied,the source path and destination path are provided as input from .net app.When I copy file using xp_cmdshell copy /y sourcepath destinationpath the file is copied but the destination filename is wierd.For example my source file looks like fpdir.txt.[2010-07-09] and i want it to be copied as fpdir.tx to destination path.but the output filename appears as fpdir.txt.[2010-07-09]fpdir.txt

here is the sp
DECLARE @SourceFile VARCHAR(1000)
DECLARE @DestFile VARCHAR(1000)

DECLARE @Cmd VARCHAR(2000)
DECLARE @Flag VARCHAR(1)

IF LEN(@FileName) > 0
BEGIN
SET @SourceFile = @SourcePath + @FileName
SET @DestFile = @DestinationPath + RTRIM(LTRIM(SUBSTRING(@FileName,1,CHARINDEX('[',@FileName,0)-2)))
SET @Cmd = 'COPY /Y "' + @SourceFile + '" "' + @DestFile + '"'

END

EXEC XP_CMDSHELL @Cmd
SET @SourceFile = ''
please let me know the solution

[Jerry Winston]

That's a very peculiar result for this code. I copied and completed(a few declarations and SET's were missing) your code and got the proper result. The only problem I could immediately identify was the line:

SET @DestFile = @DestinationPath + RTRIM(LTRIM(SUBSTRING(@FileName,1,CHARINDEX('[',@FileName,0)-2)))

Which should have had (-3):

SET @DestFile = @DestinationPath + RTRIM(LTRIM(SUBSTRING(@FileName,1,CHARINDEX('[',@FileName,0)-3)))

Here's the code:

DECLARE @FileName varchar(1000)
DECLARE @SourceFile VARCHAR(1000)
DECLARE @DestFile VARCHAR(1000)
DECLARE @SourcePath varchar(1000)
DECLARE @DestinationPath varchar(1000)
 
DECLARE @Cmd VARCHAR(2000)
DECLARE @Flag VARCHAR(1)
SET @FileName = 'fpdir.txt.[2010-07-09]'
SET @SourcePath = 'X:\tmp\'
SET @Cmd = ''
SET @Flag = ''
SET @DestinationPath = 'T:\newFol\'
SET @DestFile = ''
SET @SourceFile = ''
 
IF LEN(@FileName) > 0
BEGIN
 
SET @SourceFile = @SourcePath + @FileName
 
SET @DestFile = @DestinationPath + RTRIM(LTRIM(SUBSTRING(@FileName,1,CHARINDEX('[',@FileName,0)-3)))
 
SET @Cmd = 'COPY /Y "' + @SourceFile + '" "' + @DestFile + '"' 
 
SELECT @Cmd
END 
 

Results:

COPY /Y "X:\tmp\fpdir.txt.[2010-07-09]" "T:\newFol\fpdir.tx"

[kavijaya]

@b0010100
thanks for the response. The code is working fine.this sp was called from UI and the parameters passed where wrong it works fine...:-)

[Jerry Winston]

Glad you got it working!