Post reply

Running a SQL Server job with a .vbs script

  • August 9, 2007 at 7:28 am

    #180039

    Hi.  I am trying to set up a job in SQL Server 2005 SP2 Enterprise Edition through SQL Server Management Studio.  I want to create a simple job to do the following:  After taking a backup of a database I want to copy the backup files and some other application files to another server on my network.  

    As a test to see if I could set up the job, I created a simple .vbs script to copy one file.  It works correctly (and quickly) when I run it at the command prompt, however when I put it in a SQL Server job it just hangs and I have to manually kill it.  I set it up as an ActiveX script in the job step. 

    Below is a copy of this simple script. This is a new install of SQL Server 2005.  Are there any settings that I have to change in order to get it to work?  I would like this process automated in one job if possible.  Any other suggestions would be appreciated? 

    Const OverwriteExisting = TRUE

    strComputer = "bb7-dev"

    set objFSO = CreateObject("Scripting.FileSystemObject")

    strRemoteFile = "\\" & strComputer & "\E$\TestFile.txt"

    objFSO.CopyFile "D:\TestFile.txt", strRemoteFile, OverwriteExisting

    Set objFSO = Nothing

  • August 9, 2007 at 8:04 am

    #725725

    Ronda,

    The first thing I would look into would be the permissions of the account that is executing the job (the SQLAgent service account).  Does it have access to the network shares you are pointing to?

    Ed

  • August 9, 2007 at 1:16 pm

    #725839

    Ed,

    Thanks for the info.  SQLAgent service was running under the local admin account.  When I changed it to use the domain admin account it worked. 

  • August 10, 2007 at 2:49 pm

    #726185

    This is a very common problem and one has to be very careful how the SQLAgent is configured. I saw this issue when I was trying to create a backup across a network. After some digging I found by changing the account SQLAgent to a domain account that had access to the share on the other box things worked just fine.

    Kurt

    DBA

    RHWI, Inc

    Poughkeepsie, NY

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

Viewing 4 posts - 1 through 3 (of 3 total)

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