Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rmg1
Constraint Violating Yak Guru
256 Posts |
 Posted - 2012-02-27 : 05:07:05
|
Hi allI've got some code I need to run when another job on a different server is finished.I've got a table on the other server that will tell me the start and finish times of the job in question (it's a very simple table comprising of an ID, a start date/time and a finish date/time).What I want to do is keep checking the table until the job for today is finished and then start my code.What's the best way to periodically check the table to see when the job is finished?Here's some sample data:-run_id run_start_date_time run_complete_date_time1 2012-02-24 04:00:00.000 2012-02-24 05:17:00.0002 2012-02-25 15:16:00.000 2012-02-25 16:34:00.0003 2012-02-26 04:00:00.000 2012-02-26 05:24:00.0004 2012-02-27 04:00:00.000 2012-02-27 04:45:00.000 Obviously, the run for today would have had a null in the last field before 04:45 this morning. |
|
|
Jayam.cnu
Starting Member
45 Posts |
Posted - 2012-02-27 : 05:28:21
|
| then check the condition ... run_id is maximum and startdate should be getdate() and completed date is null then still the job running in source server other wise you can kick you job...Declare @id int set @id =0select @id =count(*) from table where startdate=getdate() and completiondate is null and run_id =(select max(runid) from table)if @id =0 exec sp_start_job msdb..jobdetails........ |
 |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-02-27 : 05:55:07
|
| But how do I get it to do nothing until the run_complete_date_time field is filled in? |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-27 : 07:06:50
|
| If you have access to the remote server, you may be able to start the job on your local server by setting up the trigger on the remote server, for example an update/insert trigger in the table that you showed.If you MUST trigger the job from your local server, you can use the WAITFOR construct to wait until the table on the remote server is updated. http://msdn.microsoft.com/en-us/library/ms187331.aspxAs to how you would set up the WAITFOR statement, if you have access to the remote server to add/modify/edit schema objects and jobs, you might be able to set up service broker and use WAITFOR receive_statement. I have not tried this myself, so I won't be able to say anything useful about the nuances involved, but might be worth investigating.If you don't have access to the remote server to set up service broker, the only choice that I can think of is to use WAITFOR DELAY to pause for a given amount of time and keep doing that in a loop until the trigger conditions are satisfied. Details of WAITFOR are also at the same URL that I gave above. |
|
|
|
|
|
|
Best way to do loop?