Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Question Unanswered: Running multiple jobs in a sequence

    I've got the stored procedure which first creates and then starts a job.
    This stored procedure can be invoked from a number of triggers (after insert, after update)
    The problem is:
    when a sequence of statements is being executed one by one (like an insert immediately followed by several updates) and the stored procedure is invoked from the propper insert or update trigger once for each statement, the jobs are created in the right order (first for insert statement, then for updates in the order of the initial statements), but are executed in the wrong order.

    Is there any explanation to this? And any solution?
    The order of such jobs execution is vital for my application.

    Thanx a lot in advance.

    Please see the enclosed screenshot for an example list of jobs. Name of the job contains time of it's creation and another column shows the time the job was executed.
    Attached Thumbnails Attached Thumbnails jobs.gif  

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Triggers creating scheduled jobs?

    Bad idea, IMHO.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    Originally posted by blindman
    Triggers creating scheduled jobs?

    Bad idea, IMHO.
    Maybe...
    But I don't see an alternative.
    The only thing i need the job to do - is to call an asp page and update a record in a table in case of failure.
    Simply using system extended stored produred (i tried to, honestly) didn't provide enough flexibility for handling failure situations and wasn't convenient for some other reasons.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just curious...what do the jobs do?

    And don't you think the overhead in this architcture is huge?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Posts
    4
    Oh my... It's a long and sad story.

    We've got two databases. One is a 'central' SQL Server database, another - 'child' MySQL database. 'child' database holds part of the information from the 'central' database and has to be continiously kept up to date. The only available communication way is email.
    When a record is being inserted or updated or deleted in one of the involved tables of the 'central' db, it has to be somehow 'replicated' to the 'child' db.
    So, 'central' db has triggers on after insert, update and delete for these tables and all of these triggers call that same stored procedure, which creates and starts a job.
    The job executes command like 'wget.exe "url to asp page with requested parameters"' and writes some data to one of the tables in case of failure.
    ASP page forms a 'replication' email, sends it to the 'child' db and writes yet some more data to one of the tables.
    That's it.

    The system is seriously complicated by a need to have several after insert and after update triggers for some of the tables involved into 'replication' ('replication'-related triggers are set to be the first triggers for these tables). And those non-'replication'-related triggers might also execute some updates against the same table... etc...

    So, yes, it might be a bit too complicated at the moment, but I don't really see an easier and more straightforward way.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why not have the triggers write records to a "transaction" table...then let a procedure "wake" up every 5 minutes and process the records in it.

    That should isolate the complicated stuff and keep the database up and running...

    Do experience performance hits?

    Also look in to sp_Lock...I would bet your spids are banging heads...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Posts
    4
    Originally posted by Brett Kaiser
    Why not have the triggers write records to a "transaction" table...then let a procedure "wake" up every 5 minutes and process the records in it.

    That should isolate the complicated stuff and keep the database up and running...

    Do experience performance hits?

    Also look in to sp_Lock...I would bet your spids are banging heads...
    Every 1 minute or so would be better. Thanks for the idea, I'll think about it.
    And the banging heads... I'll check it too.
    Thanks.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That is the method I would use. Much less complicate, much less overhead, and easier to monitor since the data to be replicated is already in a table where you can store the status of the replication.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •