Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Rename a text file, move it, using a stored procedure Expand / Collapse
Author
Message
Posted Sunday, May 1, 2005 9:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:10 AM
Points: 235, Visits: 323

I have a stored procedue that BCP's data into a table from a file called ImportData.txt in a directory C:\ImportData.

So prior to the BCP in, the file looks like this:

C:\ImportData\ImportData.txt

What I'd like to do is change the name of the file to include the date and time, and move it to a subdirectory.  This way I know which file was BCP'ed, and don't have to think about whether I have the right file next time I import.

So after, it should look like this:

C:\ImportData\Finished\ImportData_20050430_1351.txt
C:\ImportData\Finished\ImportData_yyyymmdd_hhmm.txt

I think there is a way to do this using a stored procedure.  Any help would be appreciated.  Thanks!




Post #179046
Posted Sunday, May 1, 2005 12:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:10 AM
Points: 235, Visits: 323

This will work for naming the file.  Now all I need to do is figure out how to move and rename the file.

DECLARE
@TodayDate as varchar(40),
@TodayHour as varchar(40),
@TodayMinu  as varchar(40)

SELECT @TodayDate = CONVERT(varchar(10), GETDATE(), 112)
SELECT @TodayHour = DATEPART(hh,GETDATE())
SELECT @TodayMinu = DATEPART(mi,GETDATE())

SELECT 'ImportData' + '_' + @TodayDate + '_' + @TodayHour + '_' + @TodayMinu + '.txt'




Post #179061
Posted Sunday, May 1, 2005 12:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:10 AM
Points: 235, Visits: 323

I'm getting closer:

 

EXEC master..xp_cmdshell 'REN C:\ImportData\ImportData.txt ImportData1.txt'
EXEC master..xp_cmdshell 'MOVE C:\ImportData\ImportData1.txt C:\ImportData\Finished\ImportData1.txt'


 




Post #179062
Posted Sunday, May 1, 2005 12:47 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:10 AM
Points: 235, Visits: 323

The last line of this does not work  ...  any ideas?

DECLARE
@TodayDate as varchar(40),
@TodayHour as varchar(40),
@TodayMinu  as varchar(40),
@NewFileName as varchar(100)

SELECT @TodayDate = CONVERT(varchar(10), GETDATE(), 112)
SELECT @TodayHour = DATEPART(hh,GETDATE())
SELECT @TodayMinu = DATEPART(mi,GETDATE())
SELECT @NewFileName = 'ImportData' + '_' + @TodayDate + '_' + @TodayHour + '_' + @TodayMinu + '.txt'

EXEC master..xp_cmdshell 'MOVE C:\ImportData\ImportData.txt C:\ImportData\Finished\ImportData.txt'
EXEC master..xp_cmdshell 'REN C:\ImportData\Finished\ImportData.txt ' + @NewFileName + '.txt'

 




Post #179063
Posted Monday, May 2, 2005 6:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 12,921, Visits: 32,285

the move command really just copied the file with the given name, and then deletes the previous file. there is no need to move it with the same name, and then rename it.

just use :

EXEC master..xp_cmdshell 'MOVE C:\ImportData\ImportData.txt C:\ImportData\Finished\' + @NewFileName



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #179143
Posted Monday, May 2, 2005 9:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:10 AM
Points: 235, Visits: 323

The single command works like this:

EXEC master..xp_cmdshell 'MOVE C:\ImportData\ImportData.txt C:\ImportData\Finished\ImportDataChanged.txt'

When I add the + @NewFileName it does not work

EXEC master..xp_cmdshell 'MOVE C:\ImportData\ImportData.txt C:\ImportData\Finished\'  + @NewFileName 




Post #179220
Posted Monday, May 2, 2005 9:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 12,921, Visits: 32,285

does this work for you? i just put the commadn into a string and caled the shell with the variable.

DECLARE
@TodayDate as varchar(40),
@TodayHour as varchar(40),
@TodayMinu  as varchar(40),
@NewFileName as varchar(100),
@cmdstr as varchar(128)
SELECT @TodayDate = CONVERT(varchar(10), GETDATE(), 112)
SELECT @TodayHour = DATEPART(hh,GETDATE())
SELECT @TodayMinu = DATEPART(mi,GETDATE())
SELECT @NewFileName = 'ImportData' + '_' + @TodayDate + '_' + @TodayHour + '_' + @TodayMinu + '.txt'
print @NewFileName
set @cmdstr='MOVE /Y C:\ImportData\ImportData.txt C:\ImportData\Finished\'  + @NewFileName 
print @cmdstr
EXEC master..xp_cmdshell @cmdstr

 



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #179229
Posted Monday, May 2, 2005 9:02 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:10 AM
Points: 235, Visits: 323
Yes that works well.  Thanks. 


Post #179379
Posted Friday, November 17, 2006 9:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 27, 2008 8:33 AM
Points: 1, Visits: 2

Hi, Has anyone tried renaming a file (through xp_cmdshell) which is placed in a network share? My sql server agent (to run the job) is running under a account which is having full permission on the share.. but still does not solve the problem.

Any help would be greatly appreciated.

 

Post #323890
Posted Friday, November 17, 2006 9:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:10 AM
Points: 235, Visits: 323

You probably need to use the UNC and share name to make it work.

\\192.168.0.xxx\C\YourDirectory\YourFile\

or

\\192.168.0.xxx\C$\YourDirectory\YourFile\

etc

Only guessing

 




Post #323915
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse