I have a job with a few steps(that run reports information) and i added a query i wrote to the last step.
Sometimes the job fails because of factors i can not control (application issues)
and my query basically automatically executes jobs (the commands are running with a cursor in a table i made)when the current time is after 8:45 am, so the BI team won't need to do it themselves.
I checked it a few times and it seems to work when i execute it on ssms, but for some reason when it runs as a job it ignores the time condition and just execute the jobs anyway.
The query:
DECLARE @CodeID INT
declare @codecommand nvarchar(100)
DECLARE crsr CURSOR FAST_FORWARD
FOR
SELECT CodeID, CodeCommand
FROM exec_jobs_on_error
ORDER BY codeid asc;
OPEN crsr
FETCH NEXT FROM crsr
INTO @CodeID, @CodeCommand
WHILE @@FETCH_STATUS = 0
begin
BEGIN TRY
if exists (select 'true' where LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7)) > '08:45AM')
begin
EXEC sp_executesql @codeCommand
INSERT INTO the_daily_BI_error_log
VALUES(@CodeID, 'Completed', GETDATE())
end
END TRY
BEGIN CATCH
declare @CodeError nvarchar(100)
SET @CodeError = (SELECT ERROR_MESSAGE())
insert into the_daily_BI_error_log
values(@codeid, @CodeError, GETDATE())
END CATCH
FETCH NEXT FROM crsr
INTO @CodeID, @CodeCommand
end
CLOSE crsr
DEALLOCATE crsr
Am i missing something in the condition writing?thanks.
Best Answer
You are doing a string comparison, not a time comparison. Moreover, the string you return does not include the leading
0
.You might use something like below:
Here's further proof your expression returns a string:
For me this shows an output of varchar(7).