Выполнение задания агента SQL Server из хранимой процедуры и возврата результата работы

Необходимо иметь хранимую процедуру, которая вызывает задание агента SQL Server и возвращает, успешно ли выполнялось задание или нет.

До сих пор у меня есть

CREATE PROCEDURE MonthlyData
AS
EXEC msdb.dbo.sp_start_job N'MonthlyData'

WAITFOR DELAY '000:04:00'

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

Что запускает задание, каков наилучший способ вернуться, если работа выполнена успешно или нет?

Ok сделал редактирование и использовал WAITFOR DELAY, поскольку задание обычно работает между 3-4 минутами, не превышающими 4. Есть ли работа, но есть ли более эффективный способ сделать это?

Ответ 1

Вы можете выполнить запрос:

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

Он вернет столбец run_status. Статусы:

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

Дополнительная информация о MSDN

РЕДАКТИРОВАТЬ. Вы можете опросить свою работу и убедиться, что она выполнена. Вы можете получить эту информацию из процедуры sp_help_job. Когда эта процедура возвращает статус 4, это означает, что задание неактивно. Тогда вам будет безопасно проверять его статус запуска.

Вы можете опросить, используя следующий код:

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

Этот код будет проверять состояние, ждать 3 секунды и повторить попытку. Как только мы получим статус 4, мы знаем, что работа выполнена, и безопасно проверять историю работы.

Ответ 2

Для всех вас, которым не разрешено, использовать команду OPENROWSET, это может помочь. Я нашел начало для своего решения здесь:

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

Это зависит от того, что некоторые столбцы таблицы msdb.dbo.sysjobactivity сначала заполняются после того, как задание заканчивается так или иначе.

-- 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

Возможно, вам захочется внести некоторые проверки на то, как долго будет выполняться цикл WHILE. Я решил сохранить эту часть из примера.

Руководство Microsoft для кодов выхода и т.д.: http://technet.microsoft.com/en-us/library/ms174997.aspx

Ответ 3

Возможно, я немного опоздал, но обнаружил, что для меня работал следующий запрос. Это даст время выполнения и время окончания выполнения. Вы можете изменить его, чтобы получить статус.

SELECT 
    job.name, 
    job.job_id, 
    job.originating_server, 
    activity.run_requested_date, 
    activity.stop_execution_date, 
    DATEDIFF( SECOND, activity.run_requested_date, activity.stop_execution_date ) as Elapsed 
FROM msdb.dbo.sysjobs_view job 
JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id 
JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id 
JOIN 
( 
    SELECT 
    MAX( agent_start_date ) AS max_agent_start_date 
    FROM 
    msdb.dbo.syssessions 
) sess_max 
ON sess.agent_start_date = sess_max.max_agent_start_date 
WHERE run_requested_date IS NOT NULL 
--AND stop_execution_date IS NULL 
AND job.name = @JobName