October 14, 2009 at 10:37 am
The details of this are positively ugly so to spare the cries of "NOOOOOOOOOOO" I'll spare you the security nightmare behind the request. What I need is some way of triggering a SQLAgent job on a SQL2000 box from my SQL2005 box.
There is a linked server connection between them.
I thought that sp_start_job would work with the @server_name parameter but, alas, no. At least not that I could get working.
Can anyone point me in the right direction. I'm rather hoping not to go down the hideous route of command shells and DOS commands (aka osql).
October 14, 2009 at 11:22 am
You should be able to reference the linked server to start the job on the remote server as long as the account you've created it with has sufficient privileges:
exec linkedserver.msdb.dbo.sp_start_job 'job_name'
October 15, 2009 at 4:20 am
George Parker-378530 (10/14/2009)
exec linkedserver.msdb.dbo.sp_start_job 'job_name'
Yes I thought that might work too. Sadly I haven't (yet) been able to get it to work - it's always complaining about "Job1" does not exist. It does. And if you run the same command on the local box it works fine. It's as though it's still looking locally even though you've specified the remote server in the 4 part name.
I wish there was a wallbash emoticon.
UPDATE: I've just got it working but I'm not sure which of the 100 things I've done has fixed this. More to follow for any others who are stuck with this....
October 15, 2009 at 8:19 am
Bad form replying to my own posts but for anyone else stuck as I was with "job cannot be found" when clearly the job does exist and you've been checking the spelling, using cut and paste, removing spaces from the name and nothing seems to work:
The account that is used in the linked server properties must be the same account that's configured on the sql agent job for the target server.
D'OH!
So easy when you know.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply