Looking for help exiting out of loop
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.
sql-server loops tsql sql-agent edited Nov 15 '18 at 7:54
a_horse_with_no_name
300k46458550
asked Nov 15 '18 at 3:49
user1566280user1566280
295
add a comment |
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.
sql-server loops tsql sql-agent edited Nov 15 '18 at 7:54
a_horse_with_no_name
300k46458550
asked Nov 15 '18 at 3:49
user1566280user1566280
295
Oh, yeah. Line 378. That's right about there. You might start with the time honored tradition of sprinklingprintstatements around to see where it's going and what it's doing.
– HABO
Nov 15 '18 at 4:12
add a comment |
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.
sql-server loops tsql sql-agent edited Nov 15 '18 at 7:54
a_horse_with_no_name
300k46458550
asked Nov 15 '18 at 3:49
user1566280user1566280
295
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.
sql-server loops tsql sql-agent sql-server loops tsql sql-agent edited Nov 15 '18 at 7:54
a_horse_with_no_name
300k46458550
asked Nov 15 '18 at 3:49
user1566280user1566280
295
edited Nov 15 '18 at 7:54
a_horse_with_no_name
300k46458550
asked Nov 15 '18 at 3:49
user1566280user1566280
295
edited Nov 15 '18 at 7:54
a_horse_with_no_name
300k46458550
edited Nov 15 '18 at 7:54
a_horse_with_no_name
300k46458550
edited Nov 15 '18 at 7:54
a_horse_with_no_name
300k46458550
300k46458550
asked Nov 15 '18 at 3:49
user1566280user1566280
295
asked Nov 15 '18 at 3:49
user1566280user1566280
295
asked Nov 15 '18 at 3:49
user1566280user1566280
295
295
Oh, yeah. Line 378. That's right about there. You might start with the time honored tradition of sprinklingprintstatements around to see where it's going and what it's doing.
– HABO
Nov 15 '18 at 4:12
add a comment |
Oh, yeah. Line 378. That's right about there. You might start with the time honored tradition of sprinklingprintstatements around to see where it's going and what it's doing.
– HABO
Nov 15 '18 at 4:12
Oh, yeah. Line 378. That's right about there. You might start with the time honored tradition of sprinkling
print statements around to see where it's going and what it's doing.– HABO
Nov 15 '18 at 4:12
Oh, yeah. Line 378. That's right about there. You might start with the time honored tradition of sprinkling
print statements around to see where it's going and what it's doing.– HABO
Nov 15 '18 at 4:12
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Nov 15 '18 at 7:22
Dávid LaczkóDávid Laczkó
429128
Thanks for your help!
– user1566280
Nov 15 '18 at 18:15
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53312160%2flooking-for-help-exiting-out-of-loop%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 15 '18 at 7:22
Dávid LaczkóDávid Laczkó
429128
Thanks for your help!
– user1566280
Nov 15 '18 at 18:15
add a comment |
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.
answered Nov 15 '18 at 7:22
Dávid LaczkóDávid Laczkó
429128
Thanks for your help!
– user1566280
Nov 15 '18 at 18:15
add a comment |
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.
answered Nov 15 '18 at 7:22
Dávid LaczkóDávid Laczkó
429128
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.
answered Nov 15 '18 at 7:22
Dávid LaczkóDávid Laczkó
429128
edited Nov 15 '18 at 12:13
answered Nov 15 '18 at 7:22
Dávid LaczkóDávid Laczkó
429128
answered Nov 15 '18 at 7:22
Dávid LaczkóDávid Laczkó
429128
answered Nov 15 '18 at 7:22
Dávid LaczkóDávid Laczkó
429128
429128
Thanks for your help!
– user1566280
Nov 15 '18 at 18:15
add a comment |
Thanks for your help!
– user1566280
Nov 15 '18 at 18:15
Thanks for your help!
– user1566280
Nov 15 '18 at 18:15
Thanks for your help!
– user1566280
Nov 15 '18 at 18:15
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53312160%2flooking-for-help-exiting-out-of-loop%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Oh, yeah. Line 378. That's right about there. You might start with the time honored tradition of sprinkling
printstatements around to see where it's going and what it's doing.– HABO
Nov 15 '18 at 4:12