How to Execute Job From a Stored Procedure 1

[JohnBates]

hi everyone,

I have 3 processes I need to further automate:
1. a job executes a stored proc which copies rows from Table1 to Table2
2. a Snapshot Replication job takes a snapshot of Table2
3. a Replication Distribution job sends Table2 data to the subscriber server
(These jobs were built with Enterprise Manager)
OK - All of this is working now via running these steps as scheduled jobs. But sometimes Job 1 takes longer to complete than it usually does... so Job 2 may start running before Job 1 has finished copying. Most of the time, the scheduled times work... but not always.

** In my stored proc, can I call/execute Job 2... then after Job 2 completes, execute Job 3 ? **

It seems like this would be a fairly common thing to need to do, not execute Job 2 until Job 1 has completed...

Any ideas? Thanks - John

 

[mrdenny]

Yes. Look up sp_start_job in BOL.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[vongrunt]

If I understand correctly, there are 3 separated scheduled jobs.

Can you use only one job with 3 steps instead?

 

[mrdenny]

Because these are repliation jobs he won't be able to combine them.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[JohnBates]

thanks mrdenny,

Well, sp_start_job does help a little.

I can add this to my stored proc...
exec sp_start_job @job_name = 'Job 2' replication Snapshot
exec sp_start_job @job_name = 'Job 3' the distribution agent

but then Job 2 and Job 3 will be running at the same time...
Job 3 is the Distribution job - there will not be anything to distribute until Job 2 completes.

... maybe I am misunderstanding how this sp works, but it looks as if it just starts the job and doesn't wait for the job to complete... ?

thanks, John

 

[aalmeida]

actually it the way you have on your code it should run sequencialy

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[JohnBates]

aalmeida,

It DOES run sequentially. Starts Job 1 and immediately starts Job 2 before Job 1 has completed.

John

 

[ESquared]

You mean "in parallel."

Sequentially would mean it waits for one to finish before the next runs.

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[aalmeida]

Are you jobs returning parameters using something like
return(@val) in mid strem of execution? if so that might be interpreted by the stored procedure as the job is finished making it then go to the next statemet starting the next job before it is intende to.



AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[ESquared]

aalmeida, return immediately executes a function or stored procedure.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[aalmeida]

try this:

declare @return_status int

set @return_status = 1

exec @return_status = sp_start_job @job_name = 'Job 2' replication Snapshot
if (@return_status = 0)
begin
exec @return_status = exec sp_start_job @job_name = 'Job 3' the distribution agent
END

at list it will not execute the distribution if it is not done I suppose that exec returns 0 on success and 1 on falure

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[ESquared]

I said:
>aalmeida, return immediately executes a function or stored procedure.

But I meant EXITS not executes.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[JohnBates]

aalmeida,

Thank you for your last suggestion.

I will try running the 2 jobs just that way and post the results here in a few days.

John