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.
Originally posted by blindman
Triggers creating scheduled jobs?
Bad idea, IMHO.
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.
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.
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.