September 29, 2008 at 1:48 pm
Is it possible to use the job schedule to run a batch file on another server? Haven't been able to try it yet, but I may have a reason to use the SQL Server Agent job scheduler to run a batch job on a different server. If not possible I'll install SQL Server on the other server but would rather not.
September 29, 2008 at 1:57 pm
You can run a batch file on a remote server in a number of different ways.
a) use xp_cmdShell
b) Use windows scheduler
c) vbscript
You could even use the windows task scheduler on the remote server if need be.
You just need to make sure your security it set up properly. If you could be a bit more specific on your needs we may be able to make a recommendation as to the best possible way.
-Luke.
September 29, 2008 at 2:06 pm
It's actually not a batch file - I was just using that as an example. We have SQL Server on the same server as SAS and had used the scheduler (through the Command section of the SQL Agent job scheduler) to run some SAS programs. The programs run on one drive and send data to a different drive.
We are now upgrading and separating SAS and SQL Server onto different servers, so now the job would actually run on one server and send data to another server (instead of just to different drives on the same server). I've tried it using \\servername but the job keeps failing. I was not able to find any documentation as to whether something like this is possible using the SQL Server job scheduler.
September 29, 2008 at 2:17 pm
ok, Thanks for the additional info. Yes this should be possible. It's more than likely the security settings that are causing you the heartache. These SAS programs, what do they do, how do you call them? Is this something that can be handled by the windows task scheduler or is there some sort of proprietary interface that needs to be used?
And/or, what needs to occur once the files are deposited to a particular directory on your SQL Server? Does a DTS/SSIS task then run? Perhaps a bulk import of some sorts?
There are a couple of ways you could do this...
1) USe the SAS server to execute the appropriate programs, then use osql to execute a DTS job to do the SQL stuff.
2) USe WMI scripting, a batch file etc and xp_cmdShell to run your programs on the remote server (\\servername type paths may not work cause dos doesn't like em much, you may need to map drives which will also allow you to specify user/pswd credentials as well). You could put that as the first step in a DTS job as an execute process task and then use your existing steps in your package to finish the import.
-Luke.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply