Move file from one Directory to other

Question

Move file from one Directory to other

Dhananjay-440114

Ten Centuries

Points: 1365
More actions
February 21, 2008 at 2:18 am

#183257

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

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

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 Ten Centuries Points: 1365 More actions · Answer 2

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 SSC-Dedicated Points: 30043 More actions · Answer 3

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 SSCrazy Eights Points: 9376 More actions · Answer 4

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 Ten Centuries Points: 1365 More actions · Answer 5

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 Ten Centuries Points: 1121 More actions · Answer 6

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 SSC-Dedicated Points: 30043 More actions · Answer 7

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

Isle.Of.Mull SSC Enthusiast Points: 113 More actions · Answer 8

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.

bob.lawson Old Hand Points: 319 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 10

Dhananjay-440114 (2/21/2008)
I don't want to use xp_cmdshell due to security reasons.

Heh... here we go again. 😛

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 11

Kenneth Wilhelmsson (2/27/2008)
Security concerns around xp_cmdshell is a valid point...

I've found that most of the security concerns about xp_CmdShell are mostly based on emotion rather than anything practical. Turning it off provides no real layer of security and only hinders those that need to use it. The ONLY security concern I have about is those that allow non-SA users to use it directly. It can and should be all done through stored procedures.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dhananjay-440114 Ten Centuries Points: 1365 More actions · Answer 12

The Code in my thread works fine... By the way its a long back story..:)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 13

Isle.Of.Mull (9/19/2008)
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.

That's a real shame considering the frightful lack of security that a lot of web hosting vendors display that make any concerns about xp_CmdShell pale in comparison.

Use a batch file to run a stored procedure from SQLCmd and redirect the output to a file. Or, use Powershell.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 14

Dhananjay-440114 (10/31/2013)
The Code in my thread works fine... By the way its a long back story..:)

We have the time. Fire away. 😉

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)