retrieving the task id of a task created in task runner, from within the procedure that is executed in the task
-
We have a situation in which a two steps task is created, from which the first one validated some data based on rules. This validation gets a validation_run_id, and the data it needs to validate is grouped under a 'calculation_id'. The second step is updating some info on the calculation. We now would like to retrieve the calculation_id from the sql statement held in step 2, to use it in the procedure executed in step 1. Therefor, we'd need to retrieve the task_id of the task, and the task_step_id of step 2.
Anyone any idea how to retrieve these two thingies?
-
Are these id's identity columns? If so you could use @@identity which stores the last identity value inserted
Declare @lastid as integer
Set @lastid = @@identity
-
Olivier - see if you can find this in your "sysjobsteps" system table in the "msdb" database...
**ASCII stupid question, get a stupid ANSI !!!** -
Simon,
that was easy, not 🙂 Unfortunately, the job is not inserted by the procedure that tries to retrieve its id
On the contrary, it is executed by the job...Sushila,
I found out the jobs are not identified in the system tables, but rather in our own 't_sys_tasks' and 't_sys_tasks_steps' tables. So, my topic is a bit 'ephemeric'. Yet, it could be interesting.
In the sysjobsteps table, how should I find the task I am currently being executed by?
-
Oops - my bad! - saw tasks, steps etc... and just assumed you're talking about the system tables....
why don't you post the ddls and some sample data from your 2 tables so it'd be easier for someone to come up with a solution...?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
No, not your bad, my bad.
I only found out it wasn't the system tables after my post.
What I thought would be interesting was relating to the system tables.
In our case, what we do is create tasks and steps in a UDT, which are then one by one retrieved by a real task called 'task runner'
. He creates real SQL Task Manager jobs based on the entries in our tables. But retrieving the task_id from our tables was just basic SQL...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply