May 18, 2011 at 8:15 am
Does anyone know if it's possible to create a job on one 2008 SQL Server that should not run until a job on a different 2008 SQL Server completes?
This is the situation:
Server A has a job (I'll call JOBA) on the SQL Server Agent that runs several steps which do some normalization activities to tables on Database A.
Server B is a replicated server which contains Database A plus Database B, which is not on Server A. I need to create a job (I'll call JOBB) on Server B that updates tables on Database B, but reads data from tables on Database A after they've been normalized. We already have linked server setup to Database A on the replicated Server B.
How would I make the job on Server A a predecessor to the job on Server B? Would there be a way to somehow have the JOBA update a table (last step) when it completes the normalization steps. Then the step in JOBB would run after it verifies the table update has been performed. Or maybe there's some other way to do this? It would be nice if we had scheduling software in our shop, but we don't.
Thanks in advance for help anyone can provide.
May 18, 2011 at 10:32 am
How would I make the job on Server A a predecessor to the job on Server B? Would there be a way to somehow have the JOBA update a table (last step) when it completes the normalization steps. Then the step in JOBB would run after it verifies the table update has been performed.
That's how I'd do it. In fact, I have a very similar situation: a job on one server depends on job finishing on another server. Here's what I did:
1. The last step in the job on Server A inserts a row into a "job status" table on Server B if the previous steps completed successfully. I use a DTS package to do the insert, but you could use T-SQL since you have a linked server already set up.
2. The first step in the job on Server B queries the "job status" table and if a row is found with the current date, the step succeeds and goes to the next step. If a current row isn't found, the step fails and skips the next step.
Greg
May 18, 2011 at 11:49 am
Thanks for your reply Greg! I appreciate you taking the time.
The only thing about this idea, is that I eventually DO want to run JOBB. (I thought it more after I posted.) I would need some way for it to keep checking for the table update and once it sees that it's been done, then kick off. I could just look and see how long JOBA typically runs and then schedule JOBB to start sometime after that - maybe allow an extra 30-60 mins. However, on the rare instances when JOBA takes longer for whatever reason, then 30-60 mins. may not be late enough.
May 19, 2011 at 10:55 am
Hmm, you can have the step in JOBB retry several times before failing. Maybe set retry attempts to 3 with a retry interval of 30 minutes?
You set retry attempts on the 'Advanced' page of the job step properties.
Greg
May 19, 2011 at 11:36 am
That's a great idea! Thank you so much.
May 19, 2011 at 11:43 am
This is what I would propose:
As last step of your JobA just run a ISQL script connecting to your ServerB and raising an alert at that sql instance and have sqlagent launch the job using that alert..
I would not advise to run the job directly using sp_startjob because in that case, your ServerB managers no longer have controle over the job itself.
What I mean is, even if they disable the job, sp_startjob will still start it !
Have a look at my little script to see how it works:
"help to tighten use of cmdshell or sp_start_job"
http://www.sqlservercentral.com/scripts/Miscellaneous/31032/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 19, 2011 at 12:02 pm
Thanks for your idea ALZDBA! I have never done anything like that before, but definitely something to consider. It sounds pretty slick. I will need to run it by our DBA here before I attempt it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply