0

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.

  • 1
    The condition LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7)) > '08:45AM' would work before 01:00PM, because string '08:45AM' > string '01:00PM' – Denis Rubashkin Aug 30 '18 at 12:21
3

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:

IF CONVERT(TIME,GETDATE()) > '08:45:00'

Here's further proof your expression returns a string:

sp_describe_first_result_set  N'SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7))'

For me this shows an output of varchar(7).

  • okay thanks it seems to work on my testing job – sql_girl Aug 30 '18 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.