How do I move files from one server to another using SQL JOB

Posted on 2011-04-20
Last Modified: 2012-05-11
I've written a VB Script to move files from a folder on one server to a folder on another.  If I run the script in the operating system, it runs fine but if I run it vai a SQL Job, it reports success but doesn't do the move.
Looking in the Job History, it tells me that I have a permissions problem.  I assume this is because the Local System Account that is running SQL Agent doesn't have permission on the remote server.
So, I have three quesitons:
1. How do I give the Local System Account on the server running SQL Agent permission to write files on the remote server?
2. Some posts that I've looked at suggest that if I create a .bat to call the CScript and then call the .bat from the SQL JOB, it will work.  If this is true, why would a .bat have permissions?
3. All the posts I've seen are very old.  Is there a newer better way of doing this?
Question by:iblyid
    LVL 9

    Accepted Solution

    You need to use a proxy account with suitable permissions.

    Author Comment

    Hi radcaesar, that sounds like a plan.  I'll try it out tomorrow and let you know.  I was hoping for a more elegant solution though.  I'm running this as part of a SQL Job as the preceding step is a stored procedure that creates the files to be moved using bcp.. I'm only doing it this way as I can't get bcp to write to the remote server.  Perhaps I should experiment with using a proxy account for BCP and forget he VB altogether or even opening the file system object to do the write.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
    Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now