SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rename a text file, move it, using a stored procedure


Rename a text file, move it, using a stored procedure

Author
Message
texpic
texpic
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 373

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!





texpic
texpic
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 373

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'





texpic
texpic
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 373

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'






texpic
texpic
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 373

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'





Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28311 Visits: 39955

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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

texpic
texpic
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 373

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





Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28311 Visits: 39955

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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

texpic
texpic
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 373
Yes that works well. Thanks.



Vijay-383566
Vijay-383566
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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.


texpic
texpic
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 373

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





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search