[SQL] 저장 프로 시저에서 SQL Server 에이전트 작업을 실행하고 작업 결과를 반환

저장 프로 시저에서 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

어느 작업, 작업이 성공적 여부를 실행 한 경우 다시 얻을 무슨 최선의 방법을 시작?

확인 편집을 만들어 작업이 정상적으로 결코 이상 4. 3 ~ 4 분 사이에 실행하지 않는 일을하지만, 그것을 할 수있는 더 효율적인 방법이로 WAITFOR DELAY를 사용?

해결법

1. ==============================

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

   2.OPENROWSET 명령이 힘 도움말을 사용할 수 없습니다 모든 너희들하십시오. 나는 여기에 내 솔루션의 시작을 발견 :

   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 루프 실행이 허용되는 시간을 몇 가지 검사에 넣어 할 수 있습니다. 나는 예에서 그 부분을 계속하기로 결정했습니다.

   종료 코드 등을위한 마이크로 소프트 지침 : http://technet.microsoft.com/en-us/library/ms174997.aspx

3. ==============================

   3.다음은 작업의 상태를 확인하고 이미 실행중인 경우 실행하는 스크립트입니다.

   다음은 작업의 상태를 확인하고 이미 실행중인 경우 실행하는 스크립트입니다.

   declare @xp_results table (
   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)
   
   DECLARE @job_id uniqueidentifier ;
   select @job_id = job_id from msdb.dbo.sysjobs where name = 'Job1';
   insert into @xp_results
   EXEC master.dbo.xp_sqlagent_enum_jobs 1, sa, @job_id
   
   select case when running = 1 then 'Currently Running' else '' end as running, 
   case job_state 
       when 0 then 'Not Idle or Suspended'
       when 1 then 'Executing Job'
       when 2 then 'Waiting For Thread'
       when 3 then 'Between Retries'
       when 4 then 'Idle'
       when 5 then 'Suspended'
       when 6 then 'WaitingForStepToFinish'
       when 7 then 'PerformingCompletionActions'
   end as job_state
   from @xp_results
   
   IF (select running from @xp_results) <> 1
       EXEC msdb.dbo.sp_start_job 'Job1'
   

4. ==============================

   4.나는 조금 늦게 될 수도 있지만 나는 다음과 같은 쿼리가 나를 위해 일한 것을 발견했다. 그것은 실행 시간 및 실행 종료 시간을 줄 것이다. 당신은뿐만 아니라 상태를 가져 변경할 수 있습니다.

   나는 조금 늦게 될 수도 있지만 나는 다음과 같은 쿼리가 나를 위해 일한 것을 발견했다. 그것은 실행 시간 및 실행 종료 시간을 줄 것이다. 당신은뿐만 아니라 상태를 가져 변경할 수 있습니다.

   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
   

from https://stackoverflow.com/questions/12249056/executing-sql-server-agent-job-from-a-stored-procedure-and-returning-job-result by cc-by-sa and MIT license