November 10, 2014 at 3:32 pm
Comments posted to this topic are about the item Start Job and Wait
November 19, 2014 at 12:07 pm
Hi,
Could you please post the English version of your post, just, regarding comments.
Thanks a lot for sharing your work and idea.
Aschalew
December 1, 2014 at 10:27 am
Hi!
I'll adjust the comments and put it back.
December 1, 2014 at 12:01 pm
waiting for that, thanks
December 3, 2014 at 8:55 am
Hi!
I'm from Brazil and my English is a little bad, but I try to put comments in english.
fallow the new script.
ALTER PROCEDURE [dbo].[MONITORIA_JOB] @NOME_JOB VARCHAR(200)
/*
DATE: 10/24/2014
Development by: JEFFERSON G
FUNCTION: The PROCEDURE WILL BE USED TO PERFORM THE JOBS SQL THROUGH THE TIVOLI, AND CONTROL IF THE JOB WAS PERFORMED WITH FAILURE OR SUCCESS.
EXAMPLE: EXEC MONITORIA_JOB 'JOB - SATELLITE - IMPLEMENTATION TEST'
*/
AS
--COMMAND NOT TO DISPLAY RETURNS;
SET NOCOUNT ON;
--CREATE TEMPORARY TABLE FOR STORING INFORMATION OF THE JOB OF EXECUTION
--DROP TABLE #MONITORIA_JOB
CREATE TABLE #MONITORIA_JOB
(
SERVER_ID INT,
SERVER_NAME NVARCHAR(30),
ENLIST_DATE DATETIME,
LAST_POLL_DATE DATETIME,
LAST_RUN_DATE INT,
LAST_RUN_TIME INT,
LAST_RUN_DURATION INT,
LAST_RUN_OUTCOME INT,
LAST_OUTCOME_MESSAGE NVARCHAR(1024)
);
--DECLARATION OF VARIABLE;
DECLARE
@MENSAGEM_ERRO VARCHAR(8000)
,@HOR_EXECUCAO DATETIME
,@HOR_ULT_EXECUCAO DATETIME
-- ,@NOME_JOB VARCHAR(200)
--LOADING THE PARAMETERS OF VARIABLE;
--SET @NOME_JOB = 'JOB - SATELITE - TESTE EXECUÇÃO';
SET @MENSAGEM_ERRO = '';
SET @HOR_ULT_EXECUCAO = GETDATE();
SET @HOR_EXECUCAO = GETDATE();
--PRINT @NOME_JOB;
--PRINT @HOR_EXECUCAO;
--PRINT @HOR_ULT_EXECUCAO;
--DELAY TO CREATE A DIFFERENCE IN 3 SECONDS BETWEEN THE COLLECTION OF GETDATE () FOR VARIABLEHOR_ULT_EXECUCAO AND START JOB.
WAITFOR DELAY '00:00:03'
--FAZ A EXECUÇÃO DO JOB
EXEC MSDB.DBO.SP_START_JOB @NOME_JOB
--WHILE TO INFORM THE JOB WAS FINISH
/*RULE:
THE VARIABLE @HOR_EXECUCAO And @HOR_ULT_EXECUCAO ARE STARTED WITH GETDATE().
WHILE ON THE IMPLEMENTATION OF THE FIRST The @HOR_EXECUCAO REMAINS WITHOUT AMENDMENT AND THE VARIABLE @HOR_ULT_EXECUCAO RECEIVES THE DATE AND TIME OF LAST JOB OF EXECUTION,
AS IS GREATER OR EQUAL @HOR_EXECUCAO @HOR_ULT_EXECUCAO The WHILE remain active, SO WHEN THE JOB FINISH VARIABLE receive DATE AND FINISH TIME
THE JOB AND EXITING WHILE
*/
WHILE (@HOR_EXECUCAO >= @HOR_ULT_EXECUCAO)
BEGIN
--CLEAR TEMPORARY TABLE
TRUNCATE TABLE #MONITORIA_JOB
--INSERT RESULTS IN TEMPORARY TABLE
INSERT INTO #MONITORIA_JOB
EXEC MSDB..SP_HELP_JOBSERVER @JOB_NAME = @NOME_JOB, @SHOW_LAST_RUN_DETAILS = 1
--LOADING VARIABLE @HOR_ULT_EXECUCAO WITH THE LAST START JOB FOR COMPARE IN WHILE.
SELECT @HOR_ULT_EXECUCAO = CONVERT(DATETIME, RTRIM(LAST_RUN_DATE)) + (LAST_RUN_TIME * 9 + LAST_RUN_TIME % 10000 * 6 + LAST_RUN_TIME % 100 * 10) / 216E4 FROM #MONITORIA_JOB
--DELAY FOR WAITING THE JOB FINISH;
WAITFOR DELAY '00:02:00'
-- PRINT CONVERT(VARCHAR(19),GETDATE(),121);
-- PRINT @HOR_ULT_EXECUCAO;
-- PRINT @HOR_EXECUCAO
END
--VERIFY IF THE JOB WAS RUN WITH SUCCESS OR FAILURE;
IF (SELECT COUNT(1)
FROM MSDB .. SYSJOBHISTORY H
INNER JOIN MSDB .. SYSJOBS J
ON H.JOB_ID = J.JOB_ID
WHERE J.NAME = @NOME_JOB
AND CONVERT(DATETIME, RTRIM(H.RUN_DATE)) + (H.RUN_TIME * 9 + H.RUN_TIME % 10000 * 6 + H.RUN_TIME % 100 * 10) / 216E4 >= @HOR_EXECUCAO
AND RUN_STATUS = 0 ) > 0
BEGIN
--IF JOB FINISH WITH FAILURE
SELECT @MENSAGEM_ERRO = 'O Job "' + J.NAME +
'" Step "' + CONVERT(VARCHAR(3), H.STEP_ID) + ' - ' + H.STEP_NAME +
'" foi executado com FALHA as '
+ CONVERT(VARCHAR(19), CONVERT(DATETIME, RTRIM(H.RUN_DATE)) + (H.RUN_TIME * 9 + H.RUN_TIME % 10000 * 6 + H.RUN_TIME % 100 * 10) / 216E4, 121) +
'! MENSAGEM DE ERRO: "' + h.message + '".'
FROM MSDB .. SYSJOBHISTORY H
INNER JOIN (SELECT h.job_id, Max(step_id) step_id
FROM MSDB .. SYSJOBHISTORY H
INNER JOIN MSDB .. SYSJOBS J
ON H.JOB_ID = J.JOB_ID
WHERE J.NAME = @NOME_JOB
AND CONVERT(DATETIME, RTRIM(H.RUN_DATE)) + (H.RUN_TIME * 9 + H.RUN_TIME % 10000 * 6 + H.RUN_TIME % 100 * 10) / 216E4 >= @HOR_EXECUCAO
AND RUN_STATUS = 0
GROUP BY H.JOB_ID) S
ON (H.JOB_ID = S.JOB_ID AND H.STEP_ID = S.STEP_ID)
INNER JOIN MSDB .. SYSJOBS J
ON H.JOB_ID = J.JOB_ID
WHERE J.NAME = @NOME_JOB
AND CONVERT(DATETIME, RTRIM(H.RUN_DATE)) + (H.RUN_TIME * 9 + H.RUN_TIME % 10000 * 6 + H.RUN_TIME % 100 * 10) / 216E4 >= @HOR_EXECUCAO
ORDER BY H.STEP_ID
-- The message text returned is: This is message number 5.
RAISERROR 50010 @MENSAGEM_ERRO;
-- RAISERROR (@MENSAGEM_ERRO, -- Message text.
-- 16, -- Severity,
-- 1, -- State,
-- N'number', -- First argument.
-- 5); -- Second argument.
END
ELSE
BEGIN
--IF THE JOB FINISH WITH SUCCESS;
SELECT
'O Job "' + J.NAME +
'" Step "' + CONVERT(VARCHAR(3), H.STEP_ID) + ' - ' + H.STEP_NAME +
'" foi executado com SUCESSO as '
+ CONVERT(VARCHAR(19), CONVERT(DATETIME, RTRIM(H.RUN_DATE)) + (H.RUN_TIME * 9 + H.RUN_TIME % 10000 * 6 + H.RUN_TIME % 100 * 10) / 216E4, 121) + '!' MENSAGEM
, H.RUN_STATUS EXECUCAO
FROM
MSDB .. SYSJOBHISTORY H
INNER JOIN MSDB..SYSJOBSTEPS S
ON (H.JOB_ID = S.JOB_ID AND H.STEP_ID = S.STEP_ID)
INNER JOIN MSDB .. SYSJOBS J
ON H.JOB_ID = J.JOB_ID
WHERE J.NAME = @NOME_JOB
AND CONVERT(DATETIME, RTRIM(H.RUN_DATE)) + (H.RUN_TIME * 9 + H.RUN_TIME % 10000 * 6 + H.RUN_TIME % 100 * 10) / 216E4 >= @HOR_EXECUCAO
ORDER BY H.STEP_ID
END
SET NOCOUNT OFF;
December 3, 2014 at 11:14 am
Thanks a lot!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply