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»»

SQL ADVANCE COPY/MOVE AND RENAME FUNCTIONS Expand / Collapse
Author
Message
Posted Sunday, January 13, 2013 3:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 18, 2013 12:57 AM
Points: 25, 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 only and in particular location because our application is hard coded with this behavior.

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
Post #1406460
Posted Sunday, January 13, 2013 7:10 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:58 PM
Points: 271, Visits: 1,014
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 hardest
Post #1406461
Posted Sunday, January 13, 2013 8:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 4:51 PM
Points: 7,097, Visits: 12,597
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
Post #1406469
Posted Sunday, January 13, 2013 9:55 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 36,943, Visits: 31,444
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."

(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 #1406481
Posted Sunday, January 13, 2013 10:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 4:51 PM
Points: 7,097, Visits: 12,597
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
Post #1406484
Posted Sunday, January 13, 2013 10:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:30 PM
Points: 23,215, Visits: 31,899
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.



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)
Post #1406488
Posted Sunday, January 13, 2013 10:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 18, 2013 12:57 AM
Points: 25, Visits: 92
SQLCLR is worth trying..Utility.FileSearchInternal and Utility.FileRename will do the trick... lemme try this...
many thanks for the suggestions...
Post #1406491
Posted Sunday, January 13, 2013 10:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 4:51 PM
Points: 7,097, Visits: 12,597
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
Post #1406493
Posted Sunday, January 13, 2013 10:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:30 PM
Points: 23,215, Visits: 31,899
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.



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)
Post #1406494
Posted Sunday, January 13, 2013 10:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 4:51 PM
Points: 7,097, Visits: 12,597
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
Post #1406495
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse