Sql-server – how to Update Value in local tabel when job Finished

sql-server-2008stored-procedures

I need to update some values into a database as soon as all the tasks of Executing Service have finished executing there jobs.

In other words, I want do something like status when the all jobs finished directly change this status .

I think that doing stored procedure like: 

Update (Table Name )  Set ( Field ) = 1 )

But i don't know, how to execute the stored procedure, when the job finished.

Best Answer

Because there are several apparently semi-independent jobs running, I would suggest implementing a status table and creating a trigger for that table. It depends on how complex you want to be, but let's keep it to a very simple example. Perhaps a table:

CREATE TABLE dbo.JobGroupStatus
(Job1 INT,
 Job2 INT,
 Job3 INT,
 Job4 INT,
 Job5 INT
 Job6 INT);

When the first job starts it could set all columns to 0. Then as job one finishes it would "UPDATE dbo.JobGroupStatus SET Job1 = 1", then Job2 when it finishes would update Job2, etc. Create a trigger on that table that discerns when all jobs have finished.

 CREATE TRIGGER UPD_JobGroupStatus ON dbo.JobGroupStatus
 FOR UPDATE 
 AS
 IF (SELECT Job1+Job2+Job3+Job4+Job5+Job6 
     FROM dbo.JobGroupStatus) = 6 
   Update dbo.FinishedJobGroup Set AllDone = 1;
  GO

Of course, you would need to have a periodic check that would determine that apparently all 6 jobs will not finish and report failure.

This little snippet of code is just a toy, not a serious production program. But perhaps it offers you an idea or two about how you might approach your problem.

Related Question