Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Checking SQL Job run status
IF OBJECT_ID('SP_SYS_JobRunStatus') IS NULL EXEC('CREATE PROCEDURE SP_SYS_JobRunStatus AS RETURN 0') GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /**************************************************************** -- ============================================= -- Author: Nolan Shang -- Create date: 06/06/2017 -- Description: -- Job run status checking -- It is a private utility of Nolan,Support checking remoter server ----------------------------------- -- ============================================= ******************************************************************/ ALTER PROCEDURE [dbo].[SP_SYS_JobRunStatus] @RemoteServer VARCHAR(100)='',/*If keep blank will check local server*/ @OnlyFailed BIT =0,/*0:Failed;1:All*/ @Job_ID AS varchar(100)=NULL AS BEGIN SET NOCOUNT ON DECLARE @sql NVARCHAR(max),@Prefix VARCHAR(100) SET @RemoteServer=REPLACE(REPLACE(@RemoteServer,']',''),'[','') IF @RemoteServer IN ('','.','(Local)') SET @Prefix='' ELSE SET @Prefix='['+@RemoteServer+'].' -- BEGIN TRY IF @Job_ID IS NULL SET @Job_ID = '%' /* you can specify a job id to query for a certain job*/ IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results IF OBJECT_ID('tempdb..#JobResults') IS NOT NULL DROP TABLE #JobResults IF OBJECT_ID('tempdb..#JobList') IS NOT NULL DROP TABLE #JobList IF OBJECT_ID('tempdb..#RemoteResults') IS NOT NULL DROP TABLE #RemoteResults CREATE TABLE #Results(ID INT IDENTITY(1,1),job_index INT,job_id uniqueidentifier NOT NULL, job_name SYSNAME,step_name SYSNAME,step_id INT ,Job_Start_DateTime DATETIME,Job_Duration TIME,Current_Running_Step_ID INT ,job_run_Status INT ,step_run_status INT ,job_Run_Status_Description VARCHAR(1000),step_run_status_description VARCHAR(1000),Step_Start_DateTime DATETIME,Step_Duration TIME ) CREATE TABLE #JobResults(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 ) CREATE TABLE #JobList(ID INT IDENTITY(1,1),job_index INT,job_id uniqueidentifier NOT NULL, job_name SYSNAME ,Job_Start_DateTime DATETIME,Job_Duration TIME,Current_Running_Step_ID INT ,job_run_Status INT ,job_Run_Status_Description VARCHAR(1000) ) CREATE TABLE #RemoteResults(job_id uniqueidentifier,Job_Name sysname,step_name sysname,step_id INT,last_run_outcome TINYINT ,step_run_status INT,Step_Run_Date int,Step_Run_Time int,Step_Duration int,Job_Start_DateTime datetime,Job_Duration time(7) ) -- DECLARE @sql VARCHAR(max) ,@RemoteServer VARCHAR(100)='192.1.1.22' IF @RemoteServer IN ('','.','(Local)') SET @sql='EXEC master.dbo.xp_sqlagent_enum_jobs 1, ''''' ELSE SET @sql ='SELECT * FROM OPENQUERY([' + @RemoteServer + '], ''SET FMTONLY OFF set nocount on declare @jobid UNIQUEIDENTIFIER exec master.dbo.xp_sqlagent_enum_jobs 1, ''''sa'''', @jobid'')' PRINT @sql INSERT #JobResults EXEC(@sql) PRINT '=============== Retrieve job run history =============' SET @sql='SELECT job.job_id,job.name AS Job_Name,Jobstep.step_name,Jobstep.step_id,jobInfo.last_run_outcome,s.run_status AS step_run_status ,s.run_date as Step_Run_Date,s.run_time AS Step_Run_Time,s.run_duration as Step_Duration ,e.start_execution_date AS Job_Start_DateTime,cast(e.Duration AS time) AS Job_Duration FROM '+@Prefix+'msdb.dbo.sysjobs AS job --ON r.job_id = job.job_id INNER JOIN '+@Prefix+'msdb.dbo.sysjobsteps AS Jobstep ON Jobstep.job_id = job.job_id LEFT JOIN '+@Prefix+'msdb.dbo.sysjobservers AS jobInfo ON job.job_id = jobInfo.job_id OUTER APPLY( SELECT TOP 1 start_execution_date,(ISNULL(stop_execution_date, GETDATE()) - start_execution_date) AS Duration FROM '+@Prefix+'[msdb].[dbo].[sysjobactivity] WHERE job_id = job.job_id ORDER BY start_execution_date DESC ) e OUTER APPLY( SELECT JobHistory.run_status,JobHistory.run_duration,JobHistory.run_date,JobHistory.run_time FROM '+@Prefix+'msdb.dbo.sysjobhistory as JobHistory WITH (NOLOCK) WHERE job_id = job.job_id AND JobHistory.step_id=Jobstep.step_id and convert(datetime,STR(run_date) +'' ''+STUFF(STUFF(REPLACE(STR(run_time, 6, 0), '' '', ''0''), 3, 0, '':''), 6, 0, '':'') ) >= e.start_execution_date ) s WHERE EXISTS(SELECT 0 FROM '+@Prefix+'msdb.dbo.sysjobschedules AS jobsch WHERE jobsch.job_id=job.job_id)' PRINT @sql INSERT INTO #RemoteResults EXEC(@sql) INSERT INTO #Results(job_index,job_id,job_name,step_name,step_id,step_run_status,step_run_status_description,Step_Start_DateTime,Step_Duration ,Job_Start_DateTime,Job_Duration,Current_Running_Step_ID,job_run_Status,job_Run_Status_Description) SELECT DENSE_RANK() OVER (ORDER BY r.job_id) AS job_index ,r.job_id,rr.Job_Name,rr.step_name,rr.step_id ,CASE --convert to the uniform status numbers we are using WHEN rr.step_run_status = 0 THEN 0 WHEN rr.step_run_status = 1 THEN 1 WHEN rr.step_run_status = 2 THEN 2 WHEN rr.step_run_status = 4 THEN 2 WHEN rr.step_run_status = 3 THEN 3 ELSE NULL END AS step_run_status ,CASE WHEN rr.step_run_status = 0 THEN 'Failed' WHEN rr.step_run_status = 1 THEN 'Success' WHEN rr.step_run_status = 2 THEN 'In Progress' WHEN rr.step_run_status = 4 THEN 'In Progress' WHEN rr.step_run_status = 3 THEN 'Canceled' ELSE 'Unknown' END AS step_run_status_description ,CAST(STR(rr.Step_Run_Date) AS DATETIME) + CAST(STUFF(STUFF(REPLACE(STR(rr.Step_Run_Time, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS TIME) as Step_Start_DateTime ,CAST(CAST(STUFF(STUFF(REPLACE(STR(rr.Step_Duration % 240000, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS DATETIME) AS TIME) AS Step_Duration ,rr.Job_Start_DateTime,cast(rr.Job_Duration AS time) AS Job_Duration ,r.current_step AS Current_Running_Step_ID ,CASE WHEN r.running = 0 THEN rr.last_run_outcome ELSE /*convert to the uniform status numbers (my design)*/ CASE WHEN r.job_state = 0 THEN 1 /*success*/ WHEN r.job_state = 4 THEN 1 WHEN r.job_state = 5 THEN 1 WHEN r.job_state = 1 THEN 2 /*in progress*/ WHEN r.job_state = 2 THEN 2 WHEN r.job_state = 3 THEN 2 WHEN r.job_state = 7 THEN 2 END END AS Run_Status ,CASE WHEN r.running = 0 THEN /* sysjobservers will give last run status, but does not know about current running jobs*/ CASE WHEN rr.last_run_outcome= 0 THEN 'Failed' WHEN rr.last_run_outcome = 1 THEN 'Success' WHEN rr.last_run_outcome = 3 THEN 'Canceled' ELSE 'Unknown' END /* succeeded, failed or was canceled.*/ WHEN r.job_state = 0 THEN 'Success' WHEN r.job_state = 4 THEN 'Success' WHEN r.job_state = 5 THEN 'Success' WHEN r.job_state = 1 THEN 'In Progress' WHEN r.job_state = 2 THEN 'In Progress' WHEN r.job_state = 3 THEN 'In Progress' WHEN r.job_state = 7 THEN 'In Progress' ELSE 'Unknown' END AS Run_Status_Description FROM #JobResults AS r INNER JOIN #RemoteResults AS rr ON rr.job_id=r.job_id INSERT INTO #JobList (job_index,job_id,job_name,Job_Start_DateTime,Job_Duration,Current_Running_Step_ID,job_run_Status,job_Run_Status_Description ) SELECT DISTINCT j.job_index,j.job_id,j.job_name,j.Job_Start_DateTime,j.Job_Duration,j.Current_Running_Step_ID,j.job_run_Status,j.job_Run_Status_Description FROM #Results AS j SELECT o.job_id, o.job_name,o.Job_Start_DateTime,o.Job_Duration,o.job_run_Status,o.job_Run_Status_Description,o.Current_Running_Step_ID ,r.step_id,r.step_name,r.Step_Start_DateTime,r.Step_Duration,r.step_run_status,r.step_run_status_description FROM #JobList AS o INNER JOIN #Results AS r ON r.job_index=o.job_index WHERE o.job_run_Status=CASE WHEN @OnlyFailed=1 THEN 0 ELSE o.job_run_Status END -- SET @tableHTML=REPLACE(REPLACE(@tableHTML,'<','<'),'>','>') IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results IF OBJECT_ID('tempdb..#JobResults') IS NOT NULL DROP TABLE #JobResults IF OBJECT_ID('tempdb..#JobList') IS NOT NULL DROP TABLE #JobList IF OBJECT_ID('tempdb..#RemoteResults') IS NOT NULL DROP TABLE #RemoteResults /* END TRY BEGIN CATCH PRINT ERROR_MESSAGE() RETURN -999 END CATCH */ SET NOCOUNT Off END /* EXEC dbo.SP_SYS_JobRunStatus @OnlyFailed=1 */ EXEC dbo.SP_SYS_JobRunStatus @OnlyFailed=0
run
|
edit
|
history
|
help
0
First Query
P1
forming date as nvarchar
Libros
extract part of a string
try catch, raiserror and throw
class
Demo
Agenda
Object Attributed Mapping Interview Question