I have been working to replicate the Box feature of AutoSys. I came across a solution shown here (https://dba.stackexchange.com/a/161658), that works very, very well. I started to add to it, checking outcome, and added handling of another ETL solution we use.
Where, I ran into trouble, is when I realized nothing was checking if a job was valid. I want to check this in case a job name is spelled incorrectly, or if someone removes a job. I don't want to assume a job is being executed if it isn't. I added a check for valid SQL Agent Job name. This works, if the job names are valid. However, if the job name is not valid, the procedure gets stuck in a loop displaying the error message 'NO JOB' until I stop the procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_start_job_sequence8]
(
@JobList JobSequenceTable READONLY
,@PrntJob VARCHAR(100) = 'Unknown_Job'
)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET ANSI_WARNINGS OFF;
---------------------************TRY BLOCK************---------------------
BEGIN TRY
BEGIN
DECLARE
@JobNumber TINYINT = 1
,@JobName VARCHAR(100)
,@IsRunning BIT
,@IsEnabled BIT
,@JOB_ID VARCHAR(60) = NULL
,@JOB_HIST_ID INT
,@JOB_STATUS VARCHAR(30)
,@JOB_STATUS_ID INT
,@esub VARCHAR(100)
,@ebdy VARCHAR(500)
,@Envt VARCHAR(4)
,@OVJOB_ID VARCHAR(60)
,@OVJOB_NAME VARCHAR(120)
,@JOB_TYPE CHAR(3)
,@epri VARCHAR(6);
--- Set server environment for emails
SELECT
@Envt = CASE WHEN @@SERVERNAME LIKE '%D%' THEN 'Dev'
WHEN @@SERVERNAME LIKE '%U%' THEN 'UAT'
WHEN @@SERVERNAME LIKE '%P%' THEN 'Prod'
WHEN @@SERVERNAME LIKE '%R%' THEN 'BCP'
ELSE ''
END
--- Set server environment for email priority
,@epri = CASE WHEN @@SERVERNAME LIKE '%D%' THEN 'Low'
WHEN @@SERVERNAME LIKE '%U%' THEN 'Normal'
WHEN @@SERVERNAME LIKE '%P%' THEN 'High'
WHEN @@SERVERNAME LIKE '%R%' THEN 'High'
ELSE ''
END;
BEGIN
WHILE (@JobNumber <= (SELECT
MAX(JobNumber)
FROM
@JobList
))
BEGIN
SELECT
@JobName = JobName
FROM
@JobList
WHERE
JobNumber = @JobNumber;
--VALID JOB?
IF NOT EXISTS(SELECT j.name FROM msdb.dbo.sysjobs_view J WITH(NOLOCK)
WHERE j.Name = @JobName)
BEGIN
PRINT 'NO JOB'
END;
ELSE
BEGIN
PRINT 'YES WE FOUND THE JOB';
--END
SELECT
@JOB_ID = job_id
FROM
msdb.dbo.sysjobs_view
WHERE
name = @JobName;
SELECT
@IsEnabled = enabled
FROM
msdb.dbo.sysjobs_view
WHERE
name = @JobName;
--- Very important step here. Ouvvi job names must start with Ouvvi
SELECT
@JOB_TYPE = CASE WHEN @JobName LIKE 'Ouvvi%'
THEN 'OVI'
ELSE 'SQL'
END;
--- Check if the job already running
SELECT
@IsRunning = dbo.fnJobStatusCheck(@JobName);
IF @IsRunning = 0
BEGIN
IF @IsEnabled = 0 --- Job is disabled error and send email
BEGIN
PRINT 'Job ' + @JobName
+ ' is disabled and cannot be started';
SET @esub = 'SQL Agent job '
+ @JobName + ' in ' + @Envt
+ ' is disabled and cannot be started';
SET @ebdy = 'SQL Agent job '
+ @JobName
+ ' was scheduled to run in box job '
+ @PrntJob + ' on server '
+ @@SERVERNAME + '. '
+ @JobName
+ ' could not start as it is disabled.'
+ CHAR(10) + CHAR(13)
+ +'The job ' + @JobName
+ ' should either be enabled, or removed from box job '
+ @PrntJob + '.';
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = '',
-- @recipients = 'group@mail.com'
@recipients = 'person@mail.com',
@importance = @epri,
@subject = @esub,@body = @ebdy;
END;
ELSE ---- @IsEnabled = 1
----- Job is not running nor disabled. Split for different types
---OUVVI
BEGIN
IF @JOB_TYPE = 'OVI'
BEGIN
--PRINT 'OUVVI JOB'; --- TESTING
--- Parse Ouvvi Project ID - Used for success-failure
SET @OVJOB_ID = (SELECT
RTRIM(SUBSTRING(command,
CHARINDEX('/start/',
command) + 7,3))
FROM
msdb.dbo.sysjobsteps
WHERE
job_id = @JOB_ID
AND step_id = 1
);
--- START Ouvvi Job
EXEC msdb.dbo.sp_start_job @job_name = @JobName;
--PRINT @OVJOB_ID; --- TESTING
-- Waiting for the job to finish - Ouvvi jobs don't start immediately
WAITFOR DELAY '00:00:01';
WHILE (SELECT
1
FROM
Ouvvi.dbo.Queue
WHERE
ProjectID = @OVJOB_ID
) IS NOT NULL
BEGIN
WAITFOR DELAY '00:00:15';
IF (SELECT
1
FROM
Ouvvi.dbo.Queue
WHERE
ProjectID = @OVJOB_ID
) IS NULL
BREAK;
END;
--- Get Ouvvi Job Hist ID
SET @JOB_HIST_ID = (SELECT
Instance.ID
FROM
Ouvvi.dbo.Instance
WHERE
Instance.ProjectID = @OVJOB_ID
AND Instance.EndTime = (SELECT
MAX(EndTime)
FROM
Ouvvi.dbo.Instance
WHERE
Instance.ProjectID = @OVJOB_ID
)
);
--- Get Ouvvi Result
SET @JOB_STATUS_ID = (SELECT
ISNULL(I.Result,
9)
FROM
Ouvvi.dbo.Instance I
WHERE
I.ID = @JOB_HIST_ID
);
SET @JOB_STATUS = (SELECT
CASE
WHEN I.Result = 1
THEN 'Succeeded'
WHEN I.Result = 2
THEN 'Failed'
WHEN I.Result = 3
THEN 'Cancelled'
ELSE 'Unknown'
END
FROM
Ouvvi.dbo.Instance I
WHERE
I.ID = @JOB_HIST_ID
);
IF @JOB_STATUS_ID <> 1
BEGIN
PRINT @JobName
+ ' erred with the following status: '
+ @JOB_STATUS;
SET @esub = 'Ouvvi SQL Agent job '
+ @JobName
+ ' in ' + @Envt
+ ' erred with the following status: '
+ @JOB_STATUS;
SET @ebdy = 'An Ouvvi job, scheduled in SQL Agent '
+ @JobName
+ ' has erred with the following status: '
+ @JOB_STATUS
+ ' on server '
+ @@SERVERNAME
+ '.';
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = '',
-- @recipients = 'group@mail.com'
@recipients = 'person@mail.com',
@importance = @epri,
@subject = @esub,
@body = @ebdy;
END;
END;
----Its a SQL Server Job
ELSE
BEGIN
EXEC msdb.dbo.sp_start_job @job_name = @JobName;
END;
WAITFOR DELAY '00:00:15.000';
SELECT
@IsRunning = dbo.fnJobStatusCheck(@JobName);
WHILE @IsRunning = 1
BEGIN
WAITFOR DELAY '00:00:15.000';
SELECT
@IsRunning = dbo.fnJobStatusCheck(@JobName);
END;
BEGIN
SET @JOB_HIST_ID = (SELECT
job_history_id
FROM
msdb.dbo.sysjobactivity
WHERE
job_id = @JOB_ID
AND run_requested_date = (SELECT
MAX(run_requested_date)
FROM
msdb.dbo.sysjobactivity
WHERE
job_id = @JOB_ID
)
);
SET @JOB_STATUS_ID = (SELECT
ISNULL(run_status,
9)
FROM
msdb.dbo.sysjobhistory
WHERE
instance_id = @JOB_HIST_ID
);
SET @JOB_STATUS = (SELECT
CASE
WHEN @JOB_STATUS_ID = 0
THEN 'Failed'
WHEN @JOB_STATUS_ID = 1
THEN 'Succeeded'
WHEN @JOB_STATUS_ID = 2
THEN 'Retry'
WHEN @JOB_STATUS_ID = 3
THEN 'Cancelled'
ELSE 'Unknown'
END
);
BEGIN
IF @JOB_STATUS_ID <> 1
BEGIN
PRINT @JobName
+ ' erred with the following status: '
+ @JOB_STATUS;
SET @esub = 'SQL Agent job '
+ @JobName
+ ' in ' + @Envt
+ ' erred with the following status: '
+ @JOB_STATUS;
SET @ebdy = 'SQL Agent job '
+ @JobName
+ ' erred with the following status: '
+ @JOB_STATUS
+ ' on server '
+ @@SERVERNAME
+ '.';
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = '',
-- @recipients = 'group@mail.com'
@recipients = 'person@mail.com',
@importance = @epri,
@subject = @esub,
@body = @ebdy;
END;
END;
END;
END;
SET @JOB_ID = NULL;
SET @JobNumber = @JobNumber + 1;
END;
END;
END;
END;
END;
END TRY
---------------------*********************************--------------------
---------------------************CATCH BLOCK**********-------------------
BEGIN CATCH
-- Print Error Information
DECLARE @ERRORMESSAGE NVARCHAR(4000);
DECLARE @ERRORSEVERITY INT;
DECLARE @ERRORSTATE INT;
SELECT
@ERRORMESSAGE = ERROR_MESSAGE()
,@ERRORSEVERITY = ERROR_SEVERITY()
,@ERRORSTATE = ERROR_STATE();
RAISERROR (@ERRORMESSAGE, @ERRORSEVERITY, @ERRORSTATE);
-- Rollback uncommittable transactions
IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.'
+ ' Rolling back transaction.';
ROLLBACK TRANSACTION;
END;
-- Inserting error related information into the Error Log table
INSERT INTO dbo.tbl_Object_ErrorLog
(ObjectName
,ErrorNumber
,ErrorMessage
,ErrorSeverity
,ErrorState
,ErrorlINE
,SystemUser
,LogDate
)
SELECT
ERROR_PROCEDURE()
,ERROR_NUMBER()
,ERROR_MESSAGE()
,ERROR_SEVERITY()
,ERROR_STATE()
,ERROR_LINE()
,SYSTEM_USER
,GETDATE();
END CATCH;
---------------------********************************----------------------
END;
GO
Code to launch the procedure:
SET ANSI_WARNINGS OFF
GO
DECLARE @JobList AS JobSequenceTable
INSERT INTO @JobList
VALUES
('x_test3')
,('NoJobHere')
,('x_test1')
EXEC dba.dbo.usp_start_job_sequence8 @JobList, 'TESTING'
What I want to happen is: when it checks for valid job name, prints NO JOB and ends, it should go to line 378, add 1 to @JobNumber, end that round, going to the next job.
I can't figure out why it's stuck in the loop. Thanks for any help.
As your loop is very long with nested blocks and hard to debug, I would do the following:
- before jumping in the loop, validate which jobs are missing
- create a table that holds the missing jobs, e.g. @MissingJobs, and do your stuff with them before you do with the existing jobs
- based on @MissingJobs remove the missing jobs from the @JobList table and then loop through those, so in this case no need to debug the long loop block. Or create a new table if you need the original and use that in the loop - but in that case more work to change everywhere from @JobList to the new one. You can get back the original list if you UNION ALL the reduced @JobList with @MissingJobs. Anyway you do, you need to remove the block after --VALID JOB? (till PRINT 'YES WE FOUND THE JOB';) as you can PRINT those out based on this logic - in this case you must also remove an END after SET @JobNumber = @JobNumber + 1; because you remove an unclosed ELSE BEGIN.