Delays and retries in jobs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Delays and retries in jobs

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-07-25 : 05:00:26

Hi all

I've got a job that relies on a field in another table being populated.
As the first step in the job, I have the following code:-


if ((select top 1 [run_start_date_time] from [10.222.22.42].[CCC_act_abs].[dbo].[CCC_ETL_run_log]
	order by 1 desc) > CAST(getdate() as DATE))
	and
	((select top 1 [run_complete_date_time] from [10.222.22.42].[CCC_act_abs].[dbo].[CCC_ETL_run_log]
	order by 1 desc) is  null)
begin
	insert into CCC_Data.dbo.AppLog(LogData,LogTimeDate)
	select
		'Waiting for remote data'
		,GETDATE()

	declare @test int
	set @test=1/0
end
else
	insert into CCC_Data.dbo.AppLog(LogData,LogTimeDate)
	select
		'Remote data available'
		,GETDATE()

To me, that says if start date is today and then end date is null then force an error.
Under the advanced options for this step, I've got:-
On success go to next step
Retry attempts - 99
Retry interval - 5 minutes
On failure quite reporting error

The job is supposed to start at 5:15AM
The completed date in the table I'm checking was 5:23AM but the job appeared to get to step 2 before that.

Am I missing something here, am I mis-using the retries/delays or doing something dim in the code above?

All help gratefully received.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-25 : 07:41:58

If the CCC_ETL_run_log is a cumulative log table, wouldn't it have the run_complete_date_time from yesterday (or a previous day)? If that is true, the second select in your IF condition will always be TRUE - i.e., you will not get a null.

You could change that part to something like this:

	NOT EXISTS
	( 
		SELECT * FROM [10.222.22.42].[CCC_act_abs].[dbo].[CCC_ETL_run_log]
		WHERE [run_complete_date_time] > CAST(GETDATE() AS DATE)
	)

Also, don't mean to pick on you, but somehow, generating an error by doing a divide by zero seems sort of unusual. There may be better ways to generate an error or send a return value that can be used for program flow control. I am not able to express what it is that bothers me about it though, so please feel free to ignore.

While you are at it, you might also want to consider the case where the job has not even started.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-07-25 : 08:30:37

I was thinking that if the job had started (and yes, I'm querying a cumulative log table) that the top 1 I selected would be null.
I somehow need to check that the job has started today and finished before I move to the next step and I'm open to ideas.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-25 : 08:43:21

You can use a logic such as this - you just need to fill in the code for the 3 different scenarios.

IF EXISTS
(
	SELECT * FROM [10.222.22.42].[CCC_act_abs].[dbo].[CCC_ETL_run_log]
	WHERE [run_start_date_time] > CAST(GETDATE() AS DATE)
)
BEGIN
	IF NOT EXISTS
		(
			SELECT * FROM [10.222.22.42].[CCC_act_abs].[dbo].[CCC_ETL_run_log]
			WHERE [run_complete_date_time] > CAST(GETDATE() AS DATE)
		)
	BEGIN
		PRINT 'code to handle the case where job started, but did not finish';
	END
	ELSE 
	BEGIN
		PRINT 'code to handle the case where job has started and finished.';
	END
END
ELSE
BEGIN
	PRINT 'code to handle the case where job has not even started.';
END

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-07-25 : 08:59:13

Thanks for that.
I'm getting on error to do with the WHERE clause on the first bit.
The error is:-
OLE DB provider "SQLNCLI10" for linked server "10.222.22.42" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "10.222.22.42" returned message "The scale is invalid.".
Msg 7322, Level 16, State 2, Line 1
A failure occurred while giving parameter information to OLE DB provider "SQLNCLI10" for linked server "10.222.22.42".

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-25 : 09:12:08

What is the version of SQL Server installed on the [10.222.22.42] ?If it is not SQL 2008 or later, that would cause a problem with the DATE data type. Also, you can try to run the select statement below on the server (from SSMS) to see if that works correctly.


SELECT * FROM [10.222.22.42].[CCC_act_abs].[dbo].[CCC_ETL_run_log]
	WHERE [run_start_date_time] > CAST(GETDATE() AS DATE)

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-07-25 : 09:28:19

10.222.22.42 is only on SQL 2005.
Any way round it?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-25 : 09:36:05

Change

CAST(GETDATE() AS DATE)

DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-07-25 : 10:07:22

Thanks for that, it seems to be working on a quick test.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-25 : 10:45:13

Great! Glad you got it working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources