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

Move file from one Directory to other Expand / Collapse
Author
Message
Posted Thursday, February 21, 2008 2:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 31, 2013 5:45 PM
Points: 127, Visits: 306
Hi,

I want to move file from c:\test1\test1.txt to c:\test2\test1.txt using FilesystemObject. I am using script as below:

EXEC SP_OACREATE 'SCRIPTING.FILESYSTEMOBJECT', @OBJFSYS OUT
EXEC SP_OAMETHOD @OBJFSYS, 'MOVEFILE', @FILE_NAME1,@FILE_NAME2, 1

I don't want to use xp_cmdshell. Please let me know where my code is wrong.

Regards
Post #458412
Posted Thursday, February 21, 2008 2:40 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 09, 2014 3:33 AM
Points: 1,559, Visits: 672
If you insist on using the sp_OAxx procedures, I suggest that you read up on them in BOL very carefully. If one doesn't know how to use these properly, it's a sure way to evetually blow your server out of the water.

Care to elaborate why xp_cmdshell 'copy myfile...' isn't viable?

/Kenneth



Post #458416
Posted Thursday, February 21, 2008 2:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 31, 2013 5:45 PM
Points: 127, Visits: 306
Thanks kenneth,

I don't want to use xp_cmdshell due to security reasons.

I didn't get any syntax related to copying or moving file @ BOL. If anyone used earlier pls. suggests.

Regards
Post #458419
Posted Friday, February 22, 2008 1:20 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 09, 2014 3:33 AM
Points: 1,559, Visits: 672
Ah, I didn't mean look in BOL for how to copy files using sp_OAxx procs explicitly, I ment for how to use the actual sp_OAxx procs in general. They are powerful in that they let you create OLE objects, and they'll gladly shoot your leg off if you don't do it 'right'. That's the price you pay for the 'do-it-yourself' opportunity.

Regarding security, there is an option to use xp_cmdshell with lower privileges than the default sysadmin. Though it's anyone's choice, you will probably have some security concerns regarding the security anyway. From what I can see in BOL, sp_OACreate for example, requires sysadmin to execute.

..before anyone asks, no I've never used the sp_OAxx procs (more than some experimenting long time ago), simply because I regard them as 'dangerous' and there's always been other options around. I just want to make the point clear the using sp_OAxx does indeed require that the programmer has an absolute understanding of what he/she is doing with them...

/Kenneth



Post #458948
Posted Friday, February 22, 2008 4:00 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759
I agree, using COM in server environment is tricky. The least can happen when something goes wrong is stealing memory - after some time - day, week or month, serwer will grind to halt.
If the only thing to do is to copy the file, create an user without login, give it proper credentials and wrap the copy operation into a stored proc with EXECUTE AS in its definition. This way you will be on the safe side.

Piotr


...and your only reply is slàinte mhath
Post #459007
Posted Monday, February 25, 2008 5:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 31, 2013 5:45 PM
Points: 127, Visits: 306
Hi kenneth,

No its does not require sysadmin rights to run these proc. You can specifically assign access to all 5 sp_oa .... proc. I am not getting any hint anywhere. As per BOL, the code below should work, but its not working.
Anyway i have got a way out. pls. check the code as below ..... if any one find any thing wrong in it pls. let me know......

DECLARE @oFS INT
DECLARE @oFol INT
DECLARE @oFC INT
DECLARE @oFil INT
DECLARE @Path VARCHAR(255)
DECLARE @Path1 VARCHAR(255)
DECLARE @FileName VARCHAR(255)
DECLARE @NumFiles INT
DECLARE @RetCode INT
DECLARE @ErrObject INT
DECLARE @Description VARCHAR(255)
DECLARE @Count INT

EXEC @RetCode = sp_OACreate 'Scripting.FileSystemObject', @oFS OUTPUT

SET @Path = 'D:\TEST\Test1\REN071219001.dat'
SET @Path1 = 'D:\TEST\Test2\REN071219001.dat'

EXEC @RetCode = sp_OAMethod @oFS, 'GetFile', @oFol OUTPUT, @Path
select @oFol,@RetCode

EXEC @RetCode = sp_OAMethod @oFol, 'MoveFile' , @Path,@Path1
select @RetCode

Thanks
Dhananjay
Post #459627
Posted Tuesday, February 26, 2008 2:40 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, July 05, 2013 7:49 AM
Points: 614, Visits: 215
hi there,
i'm not familiar with this kind of coding but in WMI you set the from file in a object variable (like oFrom)
and say oFrom.move ("c:\somewhere\file.ext")
so i think the information which you provide in the move statement could not work.

WMI sample:
Set fso = CreateObject("Scripting.FileSystemObject")
set oFrom = fso.getfile("c:\file.ext")
oFrom.copy("d:\file.ext")

i hope this will help you

*M*
Post #460564
Posted Wednesday, February 27, 2008 3:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 09, 2014 3:33 AM
Points: 1,559, Visits: 672
Unfortunately, I'm not much help either when it comes to OLE programming.
(It's simply not turned on on my servers) ;)

For this type of task, I use:

exec @err = master..xp_cmdshell 'move 'D:\TEST\Test1\REN071219001.dat D:\TEST\Test2\REN071219001.dat'

..and that would be the end of it.
Security concerns around xp_cmdshell is a valid point, but so is letting users create arbitrary COM objects as well, if not even more. There are ways to use xp_cmdshell with a proxy account where you can limit permissions, I'm not certain in what context a sp_OACreate object would run.

/Kenneth



Post #460750
Posted Friday, September 19, 2008 3:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 05, 2012 8:38 AM
Points: 4, Visits: 46
Unfortunately, my web hosting vendor does not allow xp_cmdshell or sp_OACreate.

I was looking at these options to move a file as well as means to export table data to a text file.

Could someone point me in the direction of another idea?

Thanks.
Post #572867
Posted Thursday, October 31, 2013 12:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 01, 2013 12:03 PM
Points: 3, Visits: 13
I use these in several stored procs. Looks like you are missing a null.

Your code:

EXEC @RetCode = sp_OAMethod @oFol, 'MoveFile' , @Path,@Path1

Try adding a null.

EXEC @RetCode = sp_OAMethod @oFol, 'MoveFile' , NULL, @Path,@Path1
Post #1510388
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse