Using FileCopy to Copy Files via T-SQL

8 Jun 2006 2:12 PM

Larry Menzin

I am looking to automate copying of files by using the FileSystem object via
OLE automation. I'm using the following code:

DECLARE @hr         int
DECLARE @ole_FileSystem        int
DECLARE @True int
DECLARE @src varchar(250), @desc varchar(2000)
DECLARE @source varchar(255), @dest varchar(255)

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @ole_FileSystem OUT

SET @source = 'C:\file.txt'
SET @dest = 'C:\log\file1.txt'

EXEC @hr = sp_OAMethod @ole_FileSystem, 'CopyFile', @source, @dest
if @hr <> 0 begin

   exec sp_OAGetErrorInfo @ole_FileSystem, @src OUT, @desc OUT

Print @src + ': ' + @desc

end

I'm getting the following error text:

ODSOLE Extended Procedure: sp_OAMethod usage: ObjPointer int IN, MethodName
varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]

Any idea on what I'm doing wrong? Are there coding examples somewhere on
using the filesystem object in T-SQL?

--
Larry Menzin
American Techsystems Corp.

8 Jun 2006 2:42 PM

Greg Larsen

Try this code:

DECLARE @hr         int
DECLARE @ole_FileSystem        int
DECLARE @True int
DECLARE @src varchar(250), @desc varchar(2000)
DECLARE @source varchar(255), @dest varchar(255)
DECLARE @method varchar(1000)

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @ole_FileSystem OUT

SET @source = 'C:\file.txt'
SET @dest = 'C:\log\file1.txt'
set @method = 'CopyFile("' + rtrim(@source) + '","' + rtrim(@dest) + '")'

EXEC @hr = sp_OAMethod @ole_FileSystem, @Method
if @hr <> 0 begin

   exec sp_OAGetErrorInfo @ole_FileSystem, @src OUT, @desc OUT

Print @src + ': ' + @desc

end

Show quote

"Larry Menzin" wrote:

> I am looking to automate copying of files by using the FileSystem object via
> OLE automation. I'm using the following code:
>
> DECLARE @hr         int
> DECLARE @ole_FileSystem        int
> DECLARE @True int
> DECLARE @src varchar(250), @desc varchar(2000)
> DECLARE @source varchar(255), @dest varchar(255)
>
> EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @ole_FileSystem OUT
>
> SET @source = 'C:\file.txt'
> SET @dest = 'C:\log\file1.txt'
>
> EXEC @hr = sp_OAMethod @ole_FileSystem, 'CopyFile', @source, @dest
> if @hr <> 0 begin
>
>    exec sp_OAGetErrorInfo @ole_FileSystem, @src OUT, @desc OUT
>
>   Print @src + ': ' + @desc
>
> end
>
> I'm getting the following error text:
>
> ODSOLE Extended Procedure: sp_OAMethod usage: ObjPointer int IN, MethodName
> varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]
>
> Any idea on what I'm doing wrong? Are there coding examples somewhere on
> using the filesystem object in T-SQL?
>
> --
> Larry Menzin
> American Techsystems Corp.

8 Jun 2006 2:54 PM

David Browne

"Larry Menzin" <LarryMen***@discussions.microsoft.com> wrote in message
news:50B75848-10D5-4B17-865F-3C8EF2264058@microsoft.com...
>I am looking to automate copying of files by using the FileSystem object
>via
> OLE automation. I'm using the following code:
>

Close. sp_OAMethod has an out parameter thay you should pass NULL to for
methods without a return value.

DECLARE @hr int
DECLARE @ole_FileSystem int
DECLARE @True int
DECLARE @src varchar(250), @desc varchar(2000)
DECLARE @source varchar(255), @dest varchar(255)

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @ole_FileSystem OUT
if @hr <> 0
begin
   exec sp_OAGetErrorInfo @ole_FileSystem, @src OUT, @desc OUT
   raiserror('Object Creation Failed 0x%x, %s, %s',16,1,@hr,@src,@desc)
   return
end

SET @source = 'C:\file.txt'
SET @dest = 'C:\file1.txt'

EXEC @hr = sp_OAMethod @ole_FileSystem, 'CopyFile',null, @source, @dest
if @hr <> 0
begin
   exec sp_OAGetErrorInfo @ole_FileSystem, @src OUT, @desc OUT
   sp_OADestroy @ole_FileSystem
   raiserror('Method Failed 0x%x, %s, %s',16,1,@hr,@src,@desc)
   return
end

cleanup:
exec @hr = sp_OADestroy @ole_FileSystem
return

David