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

Copy and rename a file using stored procedure in SQL server 2005 Expand / Collapse
Author
Message
Posted Tuesday, July 22, 2008 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 27, 2008 3:04 PM
Points: 3, Visits: 20
Hi

Is there a way I can use T-SQL in SQL Server 2005 to accomplish this:

I need to copy a timestamped file to a different folder and rename it(remove the timestamp) and finally delete the original file.

Firstly, I need to copy the file located in the folder (C:\Webservices\datasource ) to (C:\Staging)

The file is timestamped(TradeDetail04-02-08 00_00_00 .txt) and I need to remove the timestamp so that the filename becomes 'TradeDetail'.

The original filename(with timestamp) changes daily with a new timestamp but the final file name remains the same ('Trade Detail')

I tried simply renaming one of the files using xp_cmdshell:

EXEC master..xp_cmdshell 'RENAME C:\Webservices\datasource\TradeDetail04-02-08 00_00_00 .txt TradeDetail.txt'

but when I execute it there are two rows in the result:
1) The syntax of the command is incorrect.
2) Null

Does anyone know how to accomplish this without using xp_cmdshell? Or may be the right syntax to do it?



Post #538465
Posted Tuesday, July 22, 2008 12:48 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 1:26 PM
Points: 405, Visits: 1,431
paneri.rahul (7/22/2008)
Hi

I need to copy a timestamped file to a different folder and rename it(remove the timestamp) and finally delete the original file.

Firstly, I need to copy the file located in the folder (C:\Webservices\datasource ) to (C:\Staging)

The file is timestamped(TradeDetail04-02-08 00_00_00 .txt) and I need to remove the timestamp so that the filename becomes 'TradeDetail'.

The original filename(with timestamp) changes daily with a new timestamp but the final file name remains the same ('Trade Detail')

I tried simply renaming one of the files using xp_cmdshell:

EXEC master..xp_cmdshell 'RENAME C:\Webservices\datasource\TradeDetail04-02-08 00_00_00 .txt TradeDetail.txt'

but when I execute it there are two rows in the result:
1) The syntax of the command is incorrect.
2) Null

Does anyone know how to accomplish this without using xp_cmdshell? Or may be the right syntax to do it?



1. Since your filename: 'TradeDetail04-02-08 00_00_00.txt' has a space. The command does NOT recognize it, which will throw out an error.
2. No idea about the result NULL. (since I tested success or failed, both gave me NULL.)

For RENAME, it can only rename the file at the same folder. Why not use MOVE, which can move to different folder and rename the file at the same time. After that, you can delete the original one.
Post #538766
Posted Thursday, August 14, 2008 8:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 1:10 AM
Points: 9, Visits: 204
Hi all Im having exactly the same problem here does anyone know how to go about this?
Post #552765
Posted Thursday, August 14, 2008 11:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 27, 2008 3:04 PM
Points: 3, Visits: 20
Hi..
May be this would help:

First of all..make sure that the filename has no spaces

then.. enable xp_cmdshell if its disabled using:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
go

then if u want to copy files.. use copy command:

eg here I am copying all files starting with kss in the input folder to a file called kssdatasource in the datasource folder(/B is for binary,/Y for replace existing file):

EXEC master..xp_cmdshell 'COPY /Y E:\Interface\Truck_Scale_Interface\Input\kss* /B E:\Interface\Truck_Scale_Interface\Datasource\kssdatasource.csv'


If u want to Move files.. u can use 'Move' instead..
Post #552956
Posted Saturday, August 16, 2008 5:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 1:10 AM
Points: 9, Visits: 204
Hi Paneri,
Thanks very much for your timely reply. This is the perfect solution to the problem.

You are the star!
Post #553953
Posted Saturday, August 16, 2008 7:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 35,262, Visits: 31,745
You CAN have file names with spaces (although, I prefer not to). And, you can easily run commands on those file names by enclosing the filename in double quotes... like this...

EXEC master..xp_cmdshell 'RENAME "C:\Webservices\datasource\TradeDetail04-02-08 00_00_00 .txt" TradeDetail.txt'


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #553962
Posted Thursday, October 28, 2010 9:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 1, 2013 7:08 AM
Points: 3, Visits: 77
Thanks Paneri ! very handy!

Ferruccio Guicciardi
Post #1012448
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse