Advice - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Advice

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-06-19 : 10:19:46

Hi i've a jobs that runs each night to collect data and transfers it from one server to another, i run this job at 1am each night to get the data for the pervious day, the problem am thinking i am going to have is what happens if am on holidays for a week and the jobs fails there is no one else here that knows how to get the data for the days the data failed, and for me i've to set the getdate() -1 to minus 3 or 4 depending on the days that are missing, for the data transfer and the updates that are applied to it.
Am just wondering if their is a way around this?

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-19 : 14:16:21

yes, you should use the DTS or OS scheduler. In DTS, you can schedule the job and if it fails, send an alert to you or do something else to recover the error.

May the Almighty God bless us all!

Kristen
Test

22859 Posts

Posted - 2006-06-19 : 14:26:15

Presumably if the job fails just getting it to re-gather the data from the missing day/s is going to cause it to fail again - because whatever was in that data will break it again?

I tend to do this by having a table which contains the last successful "end" date for the report, and using that as the Stat date next time. If that fails tomorrow it will do 3 days, and so on. And it can send me an alert to tell me its failed too so I can bang someone over the head to straighten out whatever was goofy.

If the reason it fails is that a fragile connection is down, then when it runs tomorrow night it will run from two days data, instead of one, and Bingo!

Kristen

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-06-20 : 06:20:10

Hi Kristen

Presumably if the job fails just getting it to re-gather the data from the missing day/s is going to cause it to fail again - because whatever was in that data will break it again?.

This maybe the cause or it could be many other things like the replication could have stopped to the main server etc.. server down, connection broken etc..

I tend to do this by having a table which contains the last successful "end" date for the report, and using that as the Stat date next time. If that fails tomorrow it will do 3 days, and so on. And it can send me an alert to tell me its failed too so I can bang someone over the head to straighten out whatever was goofy.

If the reason it fails is that a fragile connection is down, then when it runs tomorrow night it will run from two days data, instead of one, and Bingo!

This is what i like to set up for my data transfer at the moment my scripts are all like this ..

Select a,b,c, etc
From table1
Where StartTime >= convert(datetime,convert(char(8),getdate()-1,112))

Maybe you could help me build something like you have, i do get the idea of keeping the last date that the job run and use that as the start date for the next job but how do yea get over not having to using the -1 ? you must use the column name of your date table were you store the dates details..
can you explain.

Thanks

Kristen
Test

22859 Posts

Posted - 2006-06-20 : 17:06:31

I'd do it something like this:


CREATE TABLE MyConfig
(
   MyKey varchar(10) NOT NULL,
   MyDate datetime NOT NULL,
   PRIMARY KEY
   (
      MyKey
   )
)
GO

-- in your code:
SELECT @StartTime = MyDate
FROM   MyConfig
WHERE  MyKey - 'MyJobKey'

SELECT @StartTime = COALESCE(@StartTime, DATEDIFF(Year, -10, GetDate())), -- Suitable default!
       @EndTime = GetDate()

-- Process data
Select a,b,c, etc
From table1
Where StartTime >= @StartTime
      AND StartTime < @EndTime

... process ...

... IMPORTANT: Abort here, or skip next step, if error

UPDATE U
SET    MyDate = @EndTime
FROM   MyConfig AS U
WHERE  MyKey = 'MyJobKey'

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources