conditional job step

  • Hi all,

    I have a job that has one step (to populate table A). There is an external app that processes data from table A and deletes all rows from it. I need to add next step to a job ONLY if table A is empty. Is it possible via GUI? Something like IF EXISTS (select 1 from A) execute step 2. The problem is that amount of time for the app to process table A is different every time.

    How would you do it?

    Thanks,

    Eugene

  • How about making the second job step script check the table with COUNT(*) & return if the table is not empty.

    If you need to wait between steps 1 & 2, could you make the second step a separate job & run it after an interval that's enough for the app to finish?

  • Hi eugene,

    Have a look at the below URL. It will help you...

    http://stackoverflow.com/questions/9530983/ssis-execute-first-task-if-condition-met-else-skip-to-next

  • Thanks for replies,

    I don't use SSIS.

    Not sure if it makes sense, but I was hoping to place the check for number of rows in table A (function call below) at the beginning of the second step, but it fails:

    CREATE FUNCTION [dbo].[num_rows_in_A_fn] ()

    RETURNS int

    BEGIN

    DECLARE @num_rows int

    SET @num_rows = (select COUNT(*) from A)

    if @num_rows = 0

    begin

    WAITFOR DELAY '00:00:10';

    -- call itself

    exec [dbo].[num_rows_in_A_fn]

    end

    RETURN @num_rows

    END

    ------

    Msg 443, Level 16, State 14, Procedure num_rows_in_queue_fn, Line 11

    Invalid use of a side-effecting operator 'WAITFOR' within a function.

    ------

    Thanks,

    Eugene

  • If your app has access to delete data on your server, does it also ave access to run a job? If the app kicks off the job only after its done its processing then you won't have any downtime between the two steps. It could use something like:

    EXEC msdb..sp_start_job @job_name= 'jobname here'



    SQL Tips and Scripts
    SQLWorks Blog

  • eugene.pipko (10/3/2012)


    Thanks for replies,

    I don't use SSIS.

    Not sure if it makes sense, but I was hoping to place the check for number of rows in table A (function call below) at the beginning of the second step, but it fails:

    CREATE FUNCTION [dbo].[num_rows_in_A_fn] ()

    RETURNS int

    BEGIN

    DECLARE @num_rows int

    SET @num_rows = (select COUNT(*) from A)

    if @num_rows = 0

    begin

    WAITFOR DELAY '00:00:10';

    -- call itself

    exec [dbo].[num_rows_in_A_fn]

    end

    RETURN @num_rows

    END

    ------

    Msg 443, Level 16, State 14, Procedure num_rows_in_queue_fn, Line 11

    Invalid use of a side-effecting operator 'WAITFOR' within a function.

    ------

    Thanks,

    Eugene

    If you want a wait loop, just do the check in the loop & exit when it passes. Do it all within the same sp - something like this:

    CREATE PROCEDURE [dbo].[RunJob] AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @num_rows int;

    SET @num_Rows = 999;

    WHILE @num_rows > 0

    BEGIN

    SET @num_rows = (select COUNT(*) from A);

    if @num_rows > 0

    WAITFOR DELAY '00:00:10';

    -- Possibly add some code to prevent an infinite loop?

    END

    -- Call job:

    exec [dbo].[TheJobYouWantToRun]

    END

  • Thank you, it is close to what I wanted to achieve.

  • I have a similar situation that I solved by adding a job step at the beginning of the job to check for rows in the target table and forcing an error if the table is not empty.

    --Throw a Divide by Zero error if there are rows in the target table

    SELECT TOP 1 1/

    CASE

    WHEN ID > 0 THEN 0

    ELSE 1

    END

    FROM TableA;

    On the Advanced tab of the job step set the On Success Action to "Go to the next step."

    Use the Retry Attempts and Retry Interval to set how many times and how often it will check again before giving up.

    For example, you could set the Retry Attempts to 5 and the Retry Interval to 1 to make it check every minute for 5 minutes before failing.

    Use the On Failure action to do whatever you want to do when it gives up; I created another step to send an email notification.

    I hope this helps

    --Garry

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply