How to run a Job from a Stored Procedure in another server?

Question

Is it possible to run a Job from a stored procedure located in a different server? If so, how?

It would be much better if you can run the SQL locally. This is definitely going to give you a performance hit having to travel to another server. — FreeAsInBeer, Apr 06 '11 at 21:44

score 3 · Answer 1 · answered Apr 07 '11 at 15:34

3

So why not consider using

exec LINKEDSERVERNAME.msdb.dbo.sp_start_job 'Job Name'

?

(didn't test it though, maybe some-unseen-answer was the same, but erased as incorrect and not working)

There must be enough privileges for linked-server-login to run the job, of course - at least it has to be job's owner.

answered Apr 07 '11 at 15:34

Aleksei Lychev

rad!, if i use this, will the stored proc that executed the Job will wait first upon finishing the job before the said proc ends? – Albert Laure Dec 19 '16 at 09:01
1

User 6675636b20796f752 : No, it won't wait. msdb.sp_start_job just actually tries to start the job and doesn't care about an outcome. – Aleksei Lychev Dec 19 '16 at 11:21
Thanks Rad! will actually use this in my implementation today. – Albert Laure Dec 19 '16 at 11:25

score 0 · Answer 2 · answered Apr 06 '11 at 21:37

0

Yep, you can use the evil that is osql:

osql -S "Remote Server" -E -Q"exec msdb.dbo.sp_start_job 'Job Name'"

(Where -E denotes using a trusted connection, you can also specify credentials using alternative osql syntax)

answered Apr 06 '11 at 21:37

Timbo

I'm not familiar with it, is it compatible with SQLServer 2000? Also, is it bad to use it or how come the 'evil' part? – Francisco Noriega Apr 06 '11 at 21:39
Or if it's a linked server - just like @Joe Stefanelli said! – Timbo Apr 06 '11 at 21:40
Yep - it's compatible with SQL 2000 - in more recent versions sqlcmd is the tool. The evil bit, to be fair, is just my pov. There are security concerns, it's hard to debug and generally using a database to call external processes isn't a "nice" pattern. – Timbo Apr 06 '11 at 21:45
Well we do have linked servers, was @Joe Stefanelli wrong or why would he erase his answer? He erased before I could test it! – Francisco Noriega Apr 06 '11 at 21:51
@Badger how exactly do you use the osql notation? I can't just execute it as a normal query can I? – Francisco Noriega Apr 06 '11 at 22:24
Yep, sorry, to run it from tsql you'll need to wrap it in an xp_cmdshell call. E.g. execute master.dbo.xp_cmdshell 'osql ... etc – Timbo Apr 06 '11 at 22:33

2 Answers2