CODEWITHSUNDEEP
sqlsql-server
G.vigneshwari
G.vigneshwari

Reputation: 21

simple SQL query to Start and monitor the agent job in SQL

(Query acquired from the link Executing SQL Server Agent Job from a stored procedure and returning job result )

What is the value of @time_constraint in the below code

WHILE @time_constraint = @ok        
    @time_constraint ?
    @ok?** Here @time_Constrain denotes???????

use msdb
-- Start job
DECLARE @job_name NVARCHAR(MAX) = 'Demo_Test'
EXEC msdb.dbo.sp_start_job @job_name = @job_name


-- Wait for job to finish
DECLARE @job_history_id AS INT = NULL

WHILE @time_constraint = @ok
BEGIN
    SELECT TOP 1 @job_history_id = activity.job_history_id
    FROM msdb.dbo.sysjobs jobs
    INNER JOIN msdb.dbo.sysjobactivity activity ON activity.job_id = jobs.job_id
    WHERE jobs.name = @job_name
    ORDER BY activity.start_execution_date DESC

    IF @job_history_id IS NULL
    BEGIN
        WAITFOR DELAY '00:00:10'
        CONTINUE
    END
    ELSE
        BREAK
END


-- Check exit code
SELECT history.run_status
FROM msdb.dbo.sysjobhistory history
WHERE history.instance_id = @job_history_id

Upvotes: 0

Views: 1487

Answers (2)

Rey
Rey

Reputation: 186

The Full reference is below, and the clue is in the comment

"You might want to put in some checks for how long the WHILE-loop is allowed to run. I chose to keep that part out of the example."

As stated in comments, you are supposed to insert a check to stop the code from running forever, for example:

DECLARE @time_constraint datetime 
DECLARE @ok datetime

SET @time_constraint = DATEADD(second, 5, GETDATE()) -- Run for 5 seconds 
SET @ok = GETDATE()

SELECT @time_constraint as future, @ok as [current]

WHILE @time_constraint > @ok BEGIN      
   SELECT @ok = GETDATE() 
END

For all you guys who are not allowed to use the OPENROWSET command, this might help. I found the start for my solution here:

http://social.msdn.microsoft.com/Forums/en-US/89659729-fea8-4df0-8057-79e0a437b658/dynamically-checking-job-status-with-tsql

This relies on the fact that some columns of the msdb.dbo.sysjobactivity table first get populated after the job finishes in one way or the other.

-- Start job
DECLARE @job_name NVARCHAR(MAX) = 'JobName'
EXEC msdb.dbo.sp_start_job @job_name = @job_name


-- Wait for job to finish
DECLARE @job_history_id AS INT = NULL

WHILE @time_constraint = @ok
BEGIN
    SELECT TOP 1 @job_history_id = activity.job_history_id
    FROM msdb.dbo.sysjobs jobs
    INNER JOIN msdb.dbo.sysjobactivity activity ON activity.job_id = jobs.job_id
    WHERE jobs.name = @job_name
    ORDER BY activity.start_execution_date DESC

    IF @job_history_id IS NULL
    BEGIN
        WAITFOR DELAY '00:00:10'
        CONTINUE
    END
    ELSE
        BREAK
END


-- Check exit code
SELECT history.run_status
FROM msdb.dbo.sysjobhistory history
WHERE history.instance_id = @job_history_id

You might want to put in some checks for how long the WHILE-loop is allowed to run. I chose to keep that part out of the example.

Microsoft guidance for exit codes etc.: http://technet.microsoft.com/en-us/library/ms174997.aspx

Upvotes: 2

EzLo
EzLo

Reputation: 14189

The loop checks where there is a record on the job history. If there is, it breaks the loop, if it doesn't then it keeps on waiting using WAITFOR. Since waiting forever might be a bad idea, there should be an additional time-constrained condition. This is what the writer meant with @time_constraint = @ok, although the names aren't the best.

For example:

DECLARE @MaxWaitingTimeInMinutes INT = 5

DECLARE @StartTime DATETIME = GETDATE()

WHILE DATEDIFF(MINUTE, @StartTime, GETDATE()) <= @MaxWaitingTimeInMinutes
BEGIN

    -- Check for job's history. Break if exists, wait if not.

END

Another example:

DECLARE @WaitUntil DATETIME = DATEADD(MINUTE, 5, GETDATE())

WHILE GETDATE() <= @WaitUntil
BEGIN

    -- Check for job's history. Break if exists, wait if not.

END

Upvotes: 0

Related Questions