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.
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.