asked on

SQL Job WaitTime

Hi all
I have an Sql Job that executes every 5 mins.
The data source A, that feeds table B, that SQL job works on has sporadic nature,
Some time the transaction arrive in sequence sometimes not.

how can I control the SQL Job to wait for 1 mins during the execution,
and what is the side effect,

Thanks
Regards

SOLUTION

lozzamoore

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slam69

hmm have posted same comment 3 times now not sure what is happening......

if its a two step process you are exectuting in one script, consider separating out the scripts into 2 steps within an sql qgent job.

if its more complicated than that then consider using an SSIS package which gives you greater control of teh data flow

ZURINET

ASKER

New Record
TransactionID      Done      Type      TransactionID      ProductID      ValidFrom                  ValidUntil

210857            0      PREIS      220000000065ws0p      8208      2011-01-31 00:00:00.000            2011-06-30 00:00:00.000
210846            0      PREIS      220000000065ws25      8208      2011-07-01 00:00:00.000            2099-12-31 00:00:00.000

Existing Record needed update
TransactionID      Done      Type      TransactionID      ProductID        ValidFrom                    ValidUntil
210837            0      PREIS      220000000065ws0p      8208      2011-01-31 00:00:00.000            2011-12-31 00:00:00.000

I need to update recordd 210857 and Insert record 210846 with a rule that the validity date (from and until) should not cross over between two records
in the destination DB

The problem is if record 210857 is delayed as the New Record info id shows,
The It cannot be processed because the date will cross each other.
Hence I need a way to tell the transaction to wait with the insert of 210846 until 201857 Arrives.

ZURINET

ASKER

typo error TransactionID = fk_TransactionID (210857),

ZURINET

ASKER

Hi slam69

I am using 2 Jobs/Scripts at the moment..
The issue is can I schedule the second job to run after 2 mins of the first one .. I mean in sync..

Regards

ASKER CERTIFIED SOLUTION

lozzamoore

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lozzamoore

If you add a PrevTranId record, then you can do an EXISTS check for this record, and a loop that WAITFOR a period of time before a retry.

This runs the risk of an endless loop, so perhaps a max cycle of 5 re-tries, before it stops and fails the insert...

L

ZURINET

ASKER

I used Triger to solve the problem