?
Solved

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

Posted on 2006-04-06
10
Medium Priority
?
1,067 Views
Last Modified: 2012-05-05
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”.
0
Comment
Question by:JayNichols
  • 4
  • 3
  • 3
10 Comments
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 16393932
What does the error scr/desc say?

Might a permissions problem.

-- Craig
0
 

Author Comment

by:JayNichols
ID: 16393986
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
 

Author Comment

by:JayNichols
ID: 16394030
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 16394059
EXEC @hr = sp_OAMethod @obj, 'CopyFile' ,NULL,  'c:\abc.log', 'c:\ABC2.LOG'
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16394075
ohh.. u found it before me ..
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16394091
JayNichols,
> However the size of the filename is still a problem

What problem you find here ?
0
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 16394099
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
 

Author Comment

by:JayNichols
ID: 16395238
Craig.  Both ways.  When we shorten it to 10 char or less it works fine, > 10 it fails the existance test.
0
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 16395342
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
 

Author Comment

by:JayNichols
ID: 16395738
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question