asked on

master.dbo.xp_cmdshell RENAME is not renaming...

I created a DTS package and one of the tasks is to rename my X_MMBS_DATA.CSV file to a filenameyyyymmdd.CSV format.

Although the same code runs on my other DTS, it does not run on this one and I can't figure it out. :(

I don't get an error message.

DECLARE @sql varchar(4000),
        @dt varchar(500)
 
-- Get date and time 
 
SELECT  @dt = 
convert(varchar(4),datepart(year,getdate()))+ ''+
right('0'+convert(varchar(3),datepart(month,getdate())),2)+''+
right('0'+convert(varchar(4),datepart(day,getdate())),2)
 
    
-- rename file
 
SELECT    
@sql = 'master.dbo.xp_cmdshell ''rename \\10.41.32.139\rootsftp$\RadAdvocate\X_MMBS_DATA.CSV  MmbsData'+ @dt+'.CSV'''
--print @sql
exec (@sql)

Open in new window

Qlemo

Any chance the file is still in use, and the rename is failing because of that? You might want to redirect error output to a log file:
sql = 'master.dbo.xp_cmdshell ''rename \\10.41.32.139\rootsftp$\RadAdvocate\X_MMBS_DATA.CSV MmbsData'+ @dt+'.CSV 2>\\10.41.32.139\rootsftp$\RadAdvocate\X_MMBS_DATA.err.log'''

epicazo

ASKER

I don't beleive the file is in use because (X_MMBS_DATA.CSV) is a newly created file by the previous task. It almost seems as it is ignoring the Exec (Sql) statement.

@sql = 'master.dbo.xp_cmdshell ''rename \\10.41.32.139\rootsftp$\RadAdvocate\X_MMBS_DATA.CSV MmbsData'+ @dt+'.CSV >\\10.41.32.139\rootsftp$\RadAdvocate\X_MMBS_DATAerr.log'''
exec (@sql)

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

epicazo

ASKER

The problem was with my permissions in the SFTP folder. lol! I appreciate your help.