asked on

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

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?

ASKER CERTIFIED SOLUTION

radcaesar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

iblyid

ASKER

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.