October 23, 2014 at 1:54 pm
patrickmcginnis59 10839 (10/23/2014)
ScottPletcher (10/23/2014)
arnipetursson (10/23/2014)
ScottPletcher (10/22/2014)
You should be able to use the session_id to do the lookup. But since session_ids are reused, and sysjobactivity keeps history, if your proc is not currently running from within a job, you'll get a bogus job_id from some earlier process. But, if it's part of a currently executing job, this should give you the current job id.
SELECT TOP (1) ja.job_id
FROM msdb.dbo.sysjobactivity ja
WHERE
ja.session_id = @@SPID
ORDER BY ja.start_execution_date DESC
Scott, correct me if I am wrong, but isn't session_id in sysjobactivity just an increasing value that is incremented each time SQL Agent is started?
When I look at syssessions, the rows correspond to restarts.
No. The session_id in sys.dm_exec_connections must be a differently-assigned session id, as obviously every connection is not made thru SQL Agent. Session_id is equivalent to spid ("server process id") in earlier versions of SQL. SQL will reuse those session_id values.
Not trying to be a spoilsport, but I didn't get a session_id in that table that equaled the @@SPID of my test agent job.
From microsoft's page:
session_id is the "ID of the session stored in the syssessions table in the msdb database."
http://msdn.microsoft.com/en-us/library/ms190484(v=sql.100).aspx
You're right. The name's the same but it's a different value. Too bad, it kills the only really easy way to link the job to a specific task.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 23, 2014 at 8:52 pm
g.britton (10/22/2014)
I'm looking for a way to get the SQL Server Agent Job ID from within a stored procedure that is being run from SQL Server Agent, either scheduled or ad-hoc.I can see the information I want in sysjobsactivitiy but I can't see how to find the row that corresponds to the job being run from within the job.
Any suggestions?
There is a SQL Agent token that you can use for this purpose. You can pass the value returned for the JOBID token into your stored proc (as a parameter to the sproc. Have a look at http://msdn.microsoft.com/en-us/library/ms175575.aspx for a little more information
October 24, 2014 at 7:12 am
happycat59 (10/23/2014)
g.britton (10/22/2014)
I'm looking for a way to get the SQL Server Agent Job ID from within a stored procedure that is being run from SQL Server Agent, either scheduled or ad-hoc.I can see the information I want in sysjobsactivitiy but I can't see how to find the row that corresponds to the job being run from within the job.
Any suggestions?
There is a SQL Agent token that you can use for this purpose. You can pass the value returned for the JOBID token into your stored proc (as a parameter to the sproc. Have a look at http://msdn.microsoft.com/en-us/library/ms175575.aspx for a little more information
That's good to know but doesn't help my situation. The proc in question is nested within other proc calls that are themselves called by a SSIS package that Sql Agent runs.
Gerald Britton, Pluralsight courses
December 5, 2014 at 8:49 am
Just wondering if anyone else has a suggestion for this problem.
Gerald Britton, Pluralsight courses
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply