Executing SQL Server Agent Job from a Stored Procedure and Returning Job Result

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

You can run the query:

EXEC msdb.dbo.sp_help_jobhistory 
    @job_name = N'MonthlyData'

It'll return a column run_status. Statuses are:

 0 - Failed
 1 - Succeeded
 2 - Retry
 3 - Canceled         

More info on MSDN

EDIT: You might want to to poll your job and make sure it's executed. You can get this information from sp_help_job procedure. When this procedure returns status of 4 it means the job is idle.
Then it's safe to check for it's run status.

You can poll using following code:

DECLARE @job_status INT
SELECT @job_status = current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;','exec msdb.dbo.sp_help_job @job_name = ''NightlyBackups''')

WHILE @job_status <> 4
BEGIN
    WAITFOR DELAY '00:00:03'
    SELECT @job_status = current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;','exec msdb.dbo.sp_help_job @job_name = ''NightlyBackups''')
END

EXEC msdb.dbo.sp_help_jobhistory 
    @job_name = N'NightlyBackups' ;
GO

This code will check for the status, wait for 3 seconds and try again. Once we get status of 4 we know the job is done and it's safe to check for the job history.

Running and checking the result of a sql agent job through a shell script

For Anyone looking for the answer to this, I came to a stored procedure that works quite well:

CREATE PROCEDURE dbo.sp_sp_start_job_wait
(
@job_name SYSNAME,
@WaitTime DATETIME = '00:00:10', --check frequency
@JobCompletionStatus INT = null OUTPUT
)
AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @job_owner sysname

-- Create temp table with xp_sqlagent_enum_jobs structure
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

SELECT @job_id = job_id FROM msdb.dbo.sysjobs
WHERE name = @job_name

SELECT @job_owner = SUSER_SNAME()

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

-- Start the job if the job is not running
IF NOT EXISTS(SELECT TOP 1 * FROM #xp_results WHERE running = 1)
EXEC msdb.dbo.sp_start_job @job_name = @job_name

-- wait a couple of seconds to compute
WAITFOR DELAY '00:00:02'

DELETE FROM #xp_results
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

WHILE EXISTS(SELECT TOP 1 * FROM #xp_results WHERE running = 1)
BEGIN

WAITFOR DELAY @WaitTime

DELETE FROM #xp_results

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

END

SELECT top 1 @JobCompletionStatus = run_status
FROM msdb.dbo.sysjobhistory
WHERE job_id = @job_id
and step_id = 0
order by run_date desc, run_time desc

IF @JobCompletionStatus = 1
PRINT 'The job ran Successful'
ELSE IF @JobCompletionStatus = 3
PRINT 'The job is Cancelled'
ELSE
BEGIN
RAISERROR ('[ERROR]:%s job is either failed or not in good state. Please check',16, 1, @job_name) WITH LOG
END

RETURN @JobCompletionStatus

GO

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

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

Select Statement yields different result when executed via SQL Server Agent

It's due to the default language of the identity that the Agent job runs under.

In your agent job add this to the script :

SET DATEFIRST 7

[or whatever day of week you expect to be deemed first day of week]

(it's connection specific, so won't affect other connections.)

Or you could change the default language of the login used by SQL Agent (or proxy if you are using one):

USE [master]
GO
ALTER LOGIN [LoginName] WITH DEFAULT_LANGUAGE = [SomeLanguage]
GO 

Ref: SET DATEFIRST

How to store the result of a SQL Job that runs a stored procedure into a table

Add another insert statement inside your first stored procedure which insert rows into the First table.

Something like.....

Declare @Count INT;  

-- Actual Insert statement in the procedure 
INSERT INTO Table1 VALUES (1) , (2) , (3)

-- Capture the number of rows inserted
SELECT @Count = @@ROWCOUNT;

-- Insert into table2 
INSERT INTO Table2([Day] ,AddedRows)
VALUES (GETDATE() , @Count)

How to start SQL Server job from a stored procedure?

You can execute the stored procedure sp_start_job in your stored procedure.

See here: http://msdn.microsoft.com/en-us/library/ms186757.aspx

---

Related Topics