SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


conditional job step


conditional job step

Author
Message
rightontarget
rightontarget
Mr or Mrs. 500
Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)

Group: General Forum Members
Points: 581 Visits: 475
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
laurie-789651
laurie-789651
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1374 Visits: 1272
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?
ard5karthick
ard5karthick
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 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
rightontarget
rightontarget
Mr or Mrs. 500
Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)

Group: General Forum Members
Points: 581 Visits: 475
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
SQLWorks
SQLWorks
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 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
laurie-789651
laurie-789651
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1374 Visits: 1272
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



rightontarget
rightontarget
Mr or Mrs. 500
Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)

Group: General Forum Members
Points: 581 Visits: 475
Thank you, it is close to what I wanted to achieve.
garry.dixon-1013362
garry.dixon-1013362
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 185
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search