Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

conditional job step Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 4:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:59 PM
Points: 121, Visits: 448
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
Post #1367330
Posted Wednesday, October 3, 2012 4:17 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, January 21, 2016 6:40 AM
Points: 405, Visits: 1,235
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?
Post #1367530
Posted Wednesday, October 3, 2012 4:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 6:05 AM
Points: 43, Visits: 162
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
Post #1367544
Posted Wednesday, October 3, 2012 10:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:59 PM
Points: 121, Visits: 448
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
Post #1367845
Posted Wednesday, October 3, 2012 10:40 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:27 AM
Points: 34, Visits: 195
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
Post #1367847
Posted Wednesday, October 3, 2012 1:44 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, January 21, 2016 6:40 AM
Points: 405, Visits: 1,235
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


Post #1368012
Posted Wednesday, October 3, 2012 5:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:59 PM
Points: 121, Visits: 448
Thank you, it is close to what I wanted to achieve.
Post #1368111
Posted Wednesday, April 6, 2016 5:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 25, 2016 6:41 PM
Points: 1, Visits: 178
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
Post #1775679
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse