Starting a job

  • 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.


    Urbis, an urban transformation company

  • Sorry, this is for SQL 2000, not 2005. Didn't realise I was posting under 2005


    Urbis, an urban transformation company

  • 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