SQL Server Performance

Schedule jobs with different parameters

Discussion in 'General Developer Questions' started by frettmaestro, Sep 15, 2003.

  1. frettmaestro New Member

    Geniouses;

    I need to run a stored procedure with some parameters at a specific time and it only needs to run once. All parameters and the time is different every time I run it, but it's always the same procedure and I need to create the schedule and parameters programatically (TSQL).

    As far as I can see the only thing I can do is to create a job using sp_add_job and sp_add_jobschedule and have it delete itself once it finishes, but something tells me that this might not be the smoothest way. Any other suggestions or preferred ways to do it...?

    --
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand"
  2. gaurav_bindlish New Member

    Why do you need to delete the job. Let the job pick up data from a table where you'll store the parameters to be passed to the job. Once the job completes successfully, delete the record from the stated table and when you want the job to execute again, insert the new data in this table.

    HTH.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. frettmaestro New Member

    But I think you missed the point of when this job needs to run. Say I have a schedule of planes leaving an airport and I need to execute a procedure at every scheduled departuretime. The planes are different and the times are all different, I cant have the job search this table all the time for new records. Please tell me if I explain poorly.

    --
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand"
  4. gaurav_bindlish New Member

    Calculate the minimum time between the departures and set this as the frequency of the job. I don't think scanning a table takes too much of resources.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  5. frettmaestro New Member

    The point is that the intervals can be everything from 2 hours to 14 days and I need the procedure to run at the *exact* time of the schedule, this is essential. I didn't want to expose what I'm actually needing it for but I guess it really doesn't matter: I'm running a betting-website with i.e. football-games and I need the market to suspend and certain other stuff to happen at kick-off and it is as I said essential that people cannot place bets after the game has started...

    --
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand"
  6. gaurav_bindlish New Member

    If the time is fixed,when a job runs, look how much time is left, wait until that time and then kick off the stored procedure in the next step. This way your job will always be running but the SP will be triggered at the right time.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  7. Twan New Member


    alternatively put the kick-off time in the football games table, and use that to validate any interactions...? This would prevent anyone from betting on an event after it has closed... I'm sure that other process can similarly be done using a data check at the time of starting the process... You could create a view and let the application select from that if you want to stuff to automatically disappear from screens, etc.

    I personally don't like jobs to do this sort of thing, especially as there is no guarantee that SQLAgent hasn't crashed at the most inappropriate time...

    Cheers
    Twan
  8. frettmaestro New Member

    Now I'm finally catching your drift Gaurav, and I didn't think of this before but I guess that it could be done. And I assume you mean to use the "waitfor delay xx"-command ...? I haven't used this command before, is it expensive? Does it use any resources worth mentioning during the wait-period?

    Twan: I do have the kick-off time in the events-table as you say but I need to perform some actions on that time aswell as prevent users from betting on it, and I can't rely on manual interactions. Gauravs method would definetly work I think...

    --
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand"
  9. Twan New Member

    Hi frettmaestro<br />ok well let's change tack then... <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />the waitfor command will leave the thread locked (in that your caller will need to wait for that time period too) you can either use a delay or set a fixed time. <br /><br />I've never known it to cause problems with resources, the SQL connection goes into an ALARM SLEEP<br /><br />Cheers<br />Twan
  10. gaurav_bindlish New Member

    The WAIT FOR command is not expensive as far as I have seen. I have a program using this in production and it works like a charm.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Share This Page