September 28, 2006 at 7:51 pm
Hi, I have this job on my sql server that needs to be run based on an insert/update trigger. the problem I'm having is sometimes the job is still running and a record is inserted/updated thus it triggers the same job which is still running. What I want is for the job to start again once it's finished. I tried using a while loop checking for @@Error <> 0, but the @@Error is always 0 even though I'm starting an already running job?
I tried using the sp_help_job to check status of the job, but I can't work out how I can store the result returned by that stored procedure into a table (so I can query it).
Can anyone help me with this? Thanks.
September 28, 2006 at 11:24 pm
hi
dont know whether the way u r doing things is correct. i have a suggestion that may help you.
Instead of trigger starting the job let the trigger write the required data into another table. Another job or windows application can keep on checking this table and if new rows inserted and start the job for each new row inserted.
of course there might be a better method....
hope this helps
"Keep Trying"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply