Problems calling a method on an OLE automation object in T-SQL

I am trying to copy a file using the copy file command and get an error which I cannot figure out.  The file exists works fine.  It's just the copy file that returns an error.  This is the code:

declare @hr int
declare @obj int
declare @src varchar(256)
declare @desc varchar(256)
declare @res int

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

IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @obj, @src OUT, @desc OUT
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END

EXEC @hr = sp_OAMethod @obj, 'FileExists', @res output, 'c:\abc.log'
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @obj, @src OUT, @desc OUT
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END

if @RES = 0
BEGIN
      SELECT 'FILE DOES NOT EXIST'
END
ELSE
BEGIN
      SELECT 'FILE EXISTS'
      EXEC @hr = sp_OAMethod @obj, 'CopyFile' ,  'c:\abc.log', 'c:\ABC2.LOG'
      IF @hr <> 0
      BEGIN
            SELECT 'ERROR COPYING FILE'
            EXEC sp_OAGetErrorInfo @obj, @src OUT, @desc OUT
            SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
            RETURN
      END
      ELSE
      BEGIN
            SELECT 'COPIED FILE'
      END
END

EXEC sp_OADestroy @obj


NOTE:-->>> This gives the “EROR COPYING FILE”.
JayNicholsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Craig YellickDatabase ArchitectCommented:
What does the error scr/desc say?

Might a permissions problem.

-- Craig
0
JayNicholsAuthor Commented:
sp_OAMethod usage:  ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT

Also, the file exists fails on filenames > 10 characters including the path.
0
JayNicholsAuthor Commented:
Found the problem: if a parameter is not used a NULL must be inserted in it's place - ,, does not work , NULL , works!

However the size of the filename is still a problem.  Do I need to start another question?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Aneesh RetnakaranDatabase AdministratorCommented:
EXEC @hr = sp_OAMethod @obj, 'CopyFile' ,NULL,  'c:\abc.log', 'c:\ABC2.LOG'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
ohh.. u found it before me ..
0
Aneesh RetnakaranDatabase AdministratorCommented:
JayNichols,
> However the size of the filename is still a problem

What problem you find here ?
0
Craig YellickDatabase ArchitectCommented:
Does the error occur when you use a constant in a test, or when passing a longer filename as a parameter?

By that I mean, if this does this work:

    EXEC @hr = sp_OAMethod @obj, 'CopyFile' ,  'c:\LongPathMame\abc.log', 'c:\LongPathName\ABC2.LOG'

But this fails:

    EXEC @hr = sp_OAMethod @obj, 'CopyFile' ,  @Name1, @Name2

Am thinking maybe the variable values are getting clipped to 10 characters.

-- Craig
0
JayNicholsAuthor Commented:
Craig.  Both ways.  When we shorten it to 10 char or less it works fine, > 10 it fails the existance test.
0
Craig YellickDatabase ArchitectCommented:
I just tested this via cut-and-paste on my SQL Server 2000 box and it worked with:

   'c:\LongFileNameTest.log'

So it's something with your machine or implementation. Makes no sense that it would fail at ten+ characters, and only on the FileCopy method.  After the failed call, is the variable value still intact with 10+ characters?  Can you call other methods that take multiple parameters? Does the NULL have anything to do with it?
0
JayNicholsAuthor Commented:
Sorry, the actions take place on the SQL server box, not the local pc where we are executing the task.  We got confused because when executing a DTS package from a local pc the results show up on the pc, not the server.  It is only when executing the DTS pkg from a job that the actions occur on the server.

I appreciate your help.  Sorry to take your time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.