SQL Clone
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-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1165 Visits: 1118
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
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37522 Visits: 14411
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 Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206053 Visits: 41952
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37522 Visits: 14411
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 Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91012 Visits: 38945
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-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 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
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37522 Visits: 14411
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 Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91012 Visits: 38945
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
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37522 Visits: 14411
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