October 2, 2012 at 4:50 pm
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
October 3, 2012 at 4:17 am
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?
October 3, 2012 at 4:47 am
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
October 3, 2012 at 10:37 am
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
October 3, 2012 at 10:40 am
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'
October 3, 2012 at 1:44 pm
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
October 3, 2012 at 5:23 pm
Thank you, it is close to what I wanted to achieve.
April 6, 2016 at 5:51 pm
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