August 4, 2014 at 3:10 am
Hi all
I've set up a SQL Server Agent job to start do various things and then start a job on a remote server.
I'm starting the remote job like this:-
EXEC [VM-DWDB].msdb.dbo.sp_start_job @job_name = 'DataWarehouse Load Activity'
Now, the calling job doesn't wait for the called job to finish before moving to the next step (which is what I want to achieve).
Two questions:-
1) Is what i want to do possible?
2) If so, how do I set up the calling job to wait for the called job to finish before moving on (and take account of success/failure to move to the right step)?
Any help gratefully received.
August 4, 2014 at 3:31 am
You will need something in the next step of your local job to poll/wait in a loop, checking the status of the remote job to see if it's finished.
sp_start_job does just that, it starts the job. Doesn't matter if remote or local. The Success that it returns is success of starting the job.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 4, 2014 at 3:37 am
Thanks Gail.
I'm assuming (and please correct me if I'm wrong) that the status is held in a table somewhere?
I'm happy to code the loop to see if the called job has finished (I'll have to do this more than once as the called job calls other jobs, etc.).
I'm looking through the sysjob* tables but I can't seem to find where to poll to see if the job has finished.
Can you point me in the right direction please?
August 4, 2014 at 3:44 am
EXEC sp_help_job <parameters>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 4, 2014 at 3:52 am
Thank you.
I assume this will need to be run on the remote server.
August 4, 2014 at 6:34 am
For that, you could create a proc on the remote server. You execute the proc remotely, and the proc can issue the start job, and then monitor for job completion before returning.
Overall, though, I think you should try to remove logic that just waits for completion and basically hard-wires the two together. Instead, have the job post some type of "I'm done" flag back to the specified caller (i.e., the flag to set is an input param to the proc).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 5, 2014 at 4:10 am
Hi all
I'm trying to use sp_help_job (as suggested by Gail) to get the current status of the job I need to track.
Basically, I want to wait for the job to complete and then send either a success or failure email based on the outcome of the job.
I can see the bits I need in sp_help_jobs but I'm struggling to store the output of it in a temp table/table variable so I can query it.
Can anyone point me in the right direction please? I've googled it, but I get lost in the different approaches/technicalities.
I've also never passed an item back to a calling procedure so I'd be grateful for any pointers on that one as well.
August 5, 2014 at 9:28 am
OK, I thought I'd got the loop sorted out but it only works on the actual server the job is running on.
I need it to run on the calling server.
This is my code so far:-
truncate table [RFT_LOOKUPS].dbo.jobinfo
INSERT INTO [RFT_LOOKUPS].dbo.jobinfo (
[job_id]
,[originating_server]
,[name]
,[enabled]
,[description]
,[start_step_id]
,[category]
,[owner]
,[notify_level_eventlog]
,[notify_level_email]
,[notify_level_netsend]
,[notify_level_page]
,[notify_email_operator]
,[notify_netsend_operator]
,[notify_page_operator]
,[delete_level]
,[date_created]
,[date_modified]
,[version_number]
,[last_run_date]
,[last_run_time]
,[last_run_outcome]
,[next_run_date]
,[next_run_time]
,[next_run_schedule_id]
,[current_execution_status]
,[current_execution_step]
,[current_retry_attempt]
,[has_step]
,[has_schedule]
,[has_target]
,[type]
)
select
*
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec [vm-dwdb].msdb.dbo.sp_help_job')
--select * from [RFT_LOOKUPS].dbo.jobinfo
while (select current_execution_status from [RFT_LOOKUPS].dbo.jobinfo where name='DataWarehouse Load Activity')<>4
begin
truncate table [RFT_LOOKUPS].dbo.jobinfo
WAITFOR DELAY '00:00:01'
INSERT INTO [RFT_LOOKUPS].dbo.jobinfo (
[job_id]
,[originating_server]
,[name]
,[enabled]
,[description]
,[start_step_id]
,[category]
,[owner]
,[notify_level_eventlog]
,[notify_level_email]
,[notify_level_netsend]
,[notify_level_page]
,[notify_email_operator]
,[notify_netsend_operator]
,[notify_page_operator]
,[delete_level]
,[date_created]
,[date_modified]
,[version_number]
,[last_run_date]
,[last_run_time]
,[last_run_outcome]
,[next_run_date]
,[next_run_time]
,[next_run_schedule_id]
,[current_execution_status]
,[current_execution_step]
,[current_retry_attempt]
,[has_step]
,[has_schedule]
,[has_target]
,[type]
)
select
*
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec [vm-dwdb].msdb.dbo.sp_help_job')
end
When I run it on the calling server, I get this error:-
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "vm-dwdb" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI" for linked server "vm-dwdb" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
I've enabled RPC and RPC OUT on the linked server properties but I'm obviously missing something.
Any help on this would be greatly appreciated.
I'll tackle passing the output of last_run_outcome back to the calling job after I've got this bit sorted.
August 5, 2014 at 9:30 am
Not something I've tried recently, bu why not
INSERT INTO...
EXECUTE msdb.dbo.sp_help AT <linked server>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2014 at 9:45 am
Basically, I want to wait for the job to complete and then send either a success or failure email based on the outcome of the job.
Again, I urge you to ignore how it's currently being done and concentrate on what is being done.
Iow, the next process shouldn't be conditioned on a specific job completing, but rather on the relevant data process(es) which that job (currently) does being completed. Just because one job does it today doesn't that in the future it won't be done by only part of that job, or by 15 separate jobs, or by a non-job process in the cloud somewhere. Therefore, rather than triggering success based on the current "container" (job), have each significant step in the process flag somewhere that that specific data step is complete. When all required flags are present, the other process can begin, as all necessary pre-processing has completed, regardless of how/when it was done. [Yes, there's additional complexity, you'll obviously need dates and/or counters to be sure the flags are current.]
That way, you're not hard-wired to a specific job or method of doing the pre-processing.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 6, 2014 at 1:43 am
Scott - I want to keep an eye on the overall job as the steps within it could change/be added to.
If I can sort out the loop then I can get this working in fairly short order.
Gail - I can't use an INSERT INTO as it gives me a NESTED INSERT INTO error.
If I can find out why the code won't run properly, I think I'm nearly there.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply