We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
240 Views
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?
Comment
Watch Question

Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.