Start Sql server agent job after a trigger
-
Is it possible to start Sql server agent job after a trigger? what i want to do is after a certain table updates a certain column, then it runs a job that has various sql stored procedures. if not possible at all, is there something similar to this?
- rafamaniac wrote:
Is it possible to start Sql server agent job after a trigger? what i want to do is after a certain table updates a certain column, then it runs a job that has various sql stored procedures. if not possible at all, is there something similar to this?
Starting an SQL Server job from T-SQL is a "Fire and Forget" asynchronous task... but I probably would wouldn't do it from a trigger. Why not just add it to whatever is causing the update? Also remember that if a job is already running, you can't run it again until it completes.
With that in mind, it may be better to have your trigger write to a "que" table and have the job run once in a while to check the "que" table. That way, you won't miss anything if you update the certain column again before the job stops.
There might be a way to do this with Service Broker but I've never used it before and I can't say I'm a fan.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
ok thank you, will see what the service broker can do.
- rafamaniac wrote:
ok thank you, will see what the service broker can do.
Jeff's queueing idea is the way I would do it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
We use sqlserver alerts ( raiserror 50000+ ) and have sqlagent respond to it and launch a given job.
Keep in mind it may take SQLAgent up to 20 seconds to respond.
ref: help to tighten use of cmdshell or sp_start_job
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
- rafamaniac wrote:
ok thank you, will see what the service broker can do.
K... just remember that's not my first choice. For me personally, it wouldn't be a choice. Like I said, I'm not a fan of it. I probably shouldn't have even mentioned it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
every day a table gets updated from a linked server we have in our sql server (a server from a telecommunication company that gives us details about sales, login time, call time etc etc ). this table tells me that when execution is 0 the update from the database from their part was successful "LastsuccessfullyEndDate". the thing is, their database can be updated randomly throughout (09h00 15h00 and even 18h00). at the moment we are executing the job manually (that runs st. procedures that runs also their data). i created a trigger that sents email to me when their database has been updated. but it would be honey on bread if we could start the job on the same trigger has the sented mail. will try your solutions. this is a linked server i think this has to be keep in mind.
-
This was removed by the editor as SPAM
-
I would add a check in the agent job - and then schedule the agent job to run every xx time. The check looks at the table - if the LastSuccessfullyEndDate is greater than current date/time minus xx time, then a new update has occurred - so run your process.
I would probably schedule the job to run every hour, and check for a successful time after: DATEADD(hour, -1, getdate()).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Johan Bijnens wrote:
Keep in mind it may take SQLAgent up to 20 seconds to respond.
I'm the same way until I have two 12 ouncers of coffee under my belt. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Johan Bijnens wrote:
Keep in mind it may take SQLAgent up to 20 seconds to respond.
I'm the same way until I have two 12 ouncers of coffee under my belt. 😀
LOL
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
You can start the job easily from the trigger using:
EXEC msdb.dbo.sp_start_job @job_name = '<your_job_name_here>'
That will just start the job and immediately return to the trigger (that is, I'm confirming that this command does not wait for the job to complete).
Just make sure, of course, that the trigger has authority to start a job.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
That is not advisable as this will always start the job, even when it is disabled !
My point is: When a sysadmin or "sqlagent job manager" disables a job for certain maintenance time(s), when sp_startjob is used, the job will always get launched ! This may interfere with ongoing job-disabled-downtime.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
- Johan Bijnens wrote:
That is not advisable as this will always start the job, even when it is disabled !
My point is: When a sysadmin or "sqlagent job manager" disables a job for certain maintenance time(s), when sp_startjob is used, the job will always get launched ! This may interfere with ongoing job-disabled-downtime.
If you want to, you can always check to verify that the job is enabled before starting it. So that concern won't prevent you from using this method. You could even have the trigger set a flag somewhere if the job was disabled that would start the job later if you deemed that to be really necessary.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply