locked
VB script for file copy RRS feed

  • Question

  • Hi 

       Im a beginner and this is a basic vb script to copy a file from a network location.  This works sometimes but  fails at times with error " File not Found" .  I don't understand why it should fail sometimes.  The below is the code.

    Function Main()
    dim fso, f

    Set fso = CreateObject("Scripting.FileSystemObject")
    set f = fso.getfile("\\servername\X$\jobs\my file copy.mdb")
    f.delete
    set f = fso.getfile("\\servername\my-folder$\my data\access files\folder\Databases\my file.mdb")
    f.copy ("\\servername\X$\jobs\my file Copy.mdb")
    set fso = nothing
    Main = DTSTaskExecResult_Success

    End FUNCTION


    Mli.a | Mark the answers if it helps to solve your problem |

    • Moved by Bill_Stewart Wednesday, February 18, 2015 9:47 PM Unanswerable drive-by question
    Thursday, December 25, 2014 5:06 PM

All replies

  • Which line generates the error message? It could be one of several!
    Did you check whether the source or the target file might be locked or hidden?
    Thursday, December 25, 2014 5:15 PM
  • It gives error on line 4.  I know the target file is not used so it cannot be locked, but there may be chances of source file getting locked.

    Is there a way to see if its due to lock or any alternate way to copy...Thanks for your response.


    Mli.a | Mark the answers if it helps to solve your problem |

    Thursday, December 25, 2014 5:32 PM
  • Here is the easiest why to copy/overwrite a file:

    Set fso = CreateObject("Scripting.FileSystemObject")
    
    fso.CopyFile "\\servername\my-folder$\my data\access files\folder\Databases\my file.mdb", "\\servername\X$\jobs\my file copy.mdb",True
    
    Main = DTSTaskExecResult_Success
    


    ¯\_(ツ)_/¯

    Thursday, December 25, 2014 5:39 PM
  • It gives error on line 4.  I know the target file is not used so it cannot be locked, but there may be chances of source file getting locked.

    Line 4, according to the script you posted, is this one:

    Set fso = CreateObject("Scripting.FileSystemObject"

    How about quoting the actual script line to save us the trouble of counting or guessing? If you are talking about this line
    set f = fso.getfile("\\servername\X$\jobs\my file copy.mdb")
    then the answer might be simple: The file you're trying to delete does not exist! JRV's suggestion would avoid this problem.

    Thursday, December 25, 2014 5:51 PM
  • Yeah its confusing on the error line as it doesnt say it right.  I tried the one suggested by JRV and for some reason it says compilation error. 

    And im running this inside sql server job using type as ActiveX script.

    Error Msg: Cannot use parentheses when calling a Sub    Error on Line 3.

    Function Main()

    dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.CopyFile "\\servername\my-folder$\my data\access files\folder\Databases\my file.mdb", "\\servername\X$\jobs\my file copy.mdb",True
    set fso = nothing
    Main = DTSTaskExecResult_Success
    End Function


    Mli.a | Mark the answers if it helps to solve your problem |

    Thursday, December 25, 2014 8:18 PM
  • For SQL server ActiveX scripts you need to post in the MSSQLServer developers form or the SSIS forum.  Are you running this under DTS or SSIS.  Some controls are not allowed under SSIS.

    The scripts wil work as a VBS so test them as a VBS first.

    The account running SSIS/DTS does nto have access to shares or Admin shares so this will likely never work.

    The error say you are using parenthesis and you cannot when calling a sub so the code posted is not what you are running.

    Function Main()
    
        Set fso = CreateObject("Scripting.FileSystemObject")
        fso.CopyFile "\\servername\my-folder$\my data\access files\folder\Databases\my file.mdb", "\\servername\X$\jobs\my file copy.mdb",True
    
        Main = DTSTaskExecResult_Success
    
    End Function 

    Be sure you are set to run VBScript and not Jscript.


    ¯\_(ツ)_/¯

    Thursday, December 25, 2014 8:26 PM