Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL ADVANCE COPY/MOVE AND RENAME FUNCTIONS


SQL ADVANCE COPY/MOVE AND RENAME FUNCTIONS

Author
Message
nitinuniyal
nitinuniyal
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 92
Dear Experts,

I am kinda stuck into a situation where i am planning to automate one manual process.
On the daily basis we get almost 7 files from another system and to process them we have to change there name to one specific name so that system can pick it up automatically.

For example :- Daily files name is
Sql_112233.ipm
Sql_445566.ipm
Sql_778899.ipm

And to process these files in our application we have to change the file name to Sql.ipm Crazyonly and in particular location because our application is hard coded with this behavior.Angry

My idea was to copy these files out side target folder and move these file one by one and rename them as they get moved in target folder so that they can be processed as per application requirements.

Here i am looking for a way to copy these file one by one and rename them as well once they are in target folder. Can we achieve this using the combination of sql and cmd.

Please do let me know please and Many Thanks in Advance for all you help.

Regards
NU
SGT_squeequal
SGT_squeequal
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 1047
Yes you can achieve what you want all from SQL, look up xp_cmdshell, you can use this to execute DOS commands to move and rename your files

*************************************************************

The first is always the hardestw00t
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8237 Visits: 14368
I would not recommend using xp_cmdshell for this. I recommend keeping the option disabled on your instance if at all possible. Truthfully I would not do this work in T-SQL at all but if you must then I would urge you to use the SQLCLR instead.

With the introduction of SQL 2005 Microsoft began deprecating some of its own Extended Stored Procedures that it provides within SQL Server and designated the creation of new User-defined Extended Stored Procedures as a deprecated activity in favor of using the SQLCLR.

There are some SQLCLR libraries available on CodePlex that will help you interact with the file system to achieve what you want. No .NET coding will be required. Here is one that will do what you are looking to do:

http://filesystemhelper.codeplex.com/

> Utility.DirectoryList
> Utility.FileMove
> Utility.FileRename

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45045 Visits: 39898
Heh.... absolutely incredible. Someone (actually, I know/respect the fellow and I'm going to have to ask him what he's thinking) is writing CLRs to replace DOS functionality... and it's just as dangerous when you have things like "Utility.DirectoryDelete" and "Utility.DirectoryDeleteContents". I applaud the attempt at increasing security but allowing such a thing and calling it "more secure" is just insane.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8237 Visits: 14368
One of the many advantages of using the SQLCLR over xp_cmdshell for interacting with the file system is that if the user connected to SQL Server under Windows Authentication you can use their credentials to access the file system instead of a proxy account, or worse, the SQL Server service account. This would prevent a user from altering anything on the file system (local or network) for which they were not expressly granted permissions too and that is something you cannot necessarily achieve with xp_cmdshell. This is not to mention the fact that when used this way the SQLCLR allows us to audit a specific user's actions meaning the action will not show up as having been carried out by the proxy account or the SQL Server service account.

I am speaking in general though, in disagreement with the above statement that implies that using the SQLCLR offers no advantages over using xp_cmdshell when interacting with the file system, which is flatly incorrect.

That said, I am not sure if the library I linked to on CodePlex uses the connected user's credentials or not, but if that were a requirement the code could be easily modified to achieve it.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24179 Visits: 37948
opc.three (1/13/2013)
One of the many advantages of using the SQLCLR over xp_cmdshell for interacting with the file system is that if the user connected to SQL Server under Windows Authentication you can use their credentials to access the file system instead of a proxy account, or worse, the SQL Server service account. This would prevent a user from altering anything on the file system (local or network) for which they were not expressly granted permissions too and that is something you cannot necessarily achieve with xp_cmdshell. This is not to mention the fact that when used this way the SQLCLR allows us to audit a specific user's actions meaning the action will not show up as having been carried out by the proxy account or the SQL Server service account.

I am speaking in general though, in disagreement with the above statement that implies that using the SQLCLR offers no advantages over using xp_cmdshell when interacting with the file system, which is flatly incorrect.

That said, I am not sure if the library I linked to on CodePlex uses the connected user's credentials or not, but if that were a requirement the code could be easily modified to achieve it.


Of course, using SQLCLR is also dependent on whether or not you are allowed to use it in your environment as well.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
nitinuniyal
nitinuniyal
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 92
SQLCLR is worth trying..Utility.FileSearchInternal and Utility.FileRename will do the trick... lemme try this...
many thanks for the suggestions...
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8237 Visits: 14368
Lynn Pettis (1/13/2013)
Of course, using SQLCLR is also dependent on whether or not you are allowed to use it in your environment as well.

Naturally. It, like sys.xp_cmdshell, is disabled by default and must be enabled using sys.sp_configure.

EXEC sys.sp_configure 
@configname = 'clr enabled',
@configvalue = 1;
RECONFIGURE;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24179 Visits: 37948
opc.three (1/13/2013)
Lynn Pettis (1/13/2013)
Of course, using SQLCLR is also dependent on whether or not you are allowed to use it in your environment as well.

Naturally. It, like sys.xp_cmdshell, is disabled by default and must be enabled using sys.sp_configure.

EXEC sys.sp_configure 
@configname = 'clr enabled',
@configvalue = 1;
RECONFIGURE;



I know how to enable CLR. The problem with your solution comes in when you AREN"T allowed to use CLR. That is all I was saying.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8237 Visits: 14368
Lynn Pettis (1/13/2013)
I know how to enable CLR. The problem with your solution comes in when you AREN"T allowed to use CLR. That is all I was saying.

No offense meant. That was for the OP and future onlookers just to show how to enable it.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
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