Executing SQL Server Agent Job from a stored procedure and returning job result

by

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

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. Is it possible to write single query on two table which are not connected to each other?
  3. T-SQL split string
  4. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  5. How can I get column names from a table in SQL Server?
  6. Conditional WHERE clause in SQL Server
  7. How can I select the first day of a month in SQL?
  8. Split function equivalent in T-SQL?
  9. Is the NOLOCK (Sql Server hint) bad practice?
  10. Multi-statement Table Valued Function vs Inline Table Valued Function
  11. Select statement to find duplicates on certain fields
  12. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  13. Where value in column containing comma delimited values
  14. Parse comma-separated string to make IN List of strings in the Where clause
  15. SQL query to split column data into rows
  16. Common Table Expression, why semicolon?
  17. T-SQL split string based on delimiter
  18. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  19. Compare DATETIME and DATE ignoring time portion
  20. Split function in SQL Server 2008
  21. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  22. SQL “between” not inclusive
  23. Can I set ignore_dup_key on for a primary key?
  24. Using PIVOT in SQL Server 2008
  25. Which SQL query is faster? Filter on Join criteria or Where clause?
  26. How can I group by date time column without taking time into consideration
  27. How to combine results of two queries into a single dataset
  28. When using GETDATE() in many places, is it better to use a variable?
  29. Cumulative Total in MS Sql server [duplicate]
  30. COALESCE Function in TSQL

Leave a Comment