Watch for a Specific Record
-
I have a need to wait until a certain record appears in a database table before a process can start. No problem looking for the record, but how can I continue to check until the record appears? I've tried using the 'failure' condition on an "Execute SQL Task", but a task can't directly or indirectly reference itself.
The time frame for this record to show up is any where from 0100 to 0500, so I can't just delay starting the job until I'm fairly certain the record would be there.
-
Create a SQL Job with two steps. The first step checks for the record and if not found then raise an error (severity 11). Set the job to not process step 2 unless step 1 completes successfully. Set the job to run every 5 minutes or what ever interval you want between 1 and 5 am.
Here is an example for each step:
STEP #1
if not exists (select 1 from employees where employeeid =10)
RAISERROR (N'Record not present',11,1)
STEP #2 (you would put the call to your function/routine in this step
if object_id('test_') is not null drop table test_
select getdate() date_ into test_
I used the Northwind database to test. It does not have a empoyeeid = 10 record (but it does have a 9).
James.
-
Works great, thanks.
-
Your welecome. Thanks for the feedback.
James.
-
Ummm... could you use a trigger in such a case? Or would the trigger hang until the job was complete? Asking 'cause I don't know much about 2k5... stuck in the world of 2k
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You could have an endless loop? Loop while 1<2 and check for the record. When the record exists, set the condition to be false so the loop ends. Also put in a check for an abort record. When exists, exit loop.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
It all depends on the situation. A trigger is nice, but it is part of the transaction inserting the record and can slow down or error that transaction causing other issues.
The SQL Agent solution is nice because it is low overhead and uses a service that is already there to manage the process. If you can stand to have it check on a regular basis, this is a good solution.
A loop in an agent job is high overhead because it will be constantly looping.
I would tend to use the SQL agent for this, but the SQL 2005 solution for something like this is probably to use an asynchronous trigger via the service broker. This will be event driven so you do not have the overhead of something checking all the time, you run no risk of the procedure to check for the record and the one inserting it being in conflict, and the trigger will not interfere with the transaction inserting the record. The down side is you need to learn to use the service broker and if you have not done this already, stick with the Agent Job until you have something more complicated to do.
-
Sorry, should have mentioned to put a sleep in the loop. Sleep for n minutes / seconds before checking again. It will cause the same overhead as a periodically running agent job. I prefer not to create errors in jobs to ascertain some condition.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Heh... kinda what I thought... some things just don't change. Thanks Michael.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply