SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Move file from one Directory to other


Move file from one Directory to other

Author
Message
Dhananjay-440114
Dhananjay-440114
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 307
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
Kenneth Wilhelmsson
Kenneth Wilhelmsson
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2471 Visits: 699
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



Dhananjay-440114
Dhananjay-440114
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 307
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
Kenneth Wilhelmsson
Kenneth Wilhelmsson
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2471 Visits: 699
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



Piotr.Rodak
Piotr.Rodak
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1280 Visits: 1761
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
Dhananjay-440114
Dhananjay-440114
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 307
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
michael.anyone
michael.anyone
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 216
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*
Kenneth Wilhelmsson
Kenneth Wilhelmsson
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2471 Visits: 699
Unfortunately, I'm not much help either when it comes to OLE programming.
(It's simply not turned on on my servers) Wink

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



Isle.Of.Mull
Isle.Of.Mull
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 46
Unfortunately, my web hosting vendor does not allow xp_cmdshell or sp_OACreate. Sad

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.
bob.lawson
bob.lawson
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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
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