SQL Server Agent - Can Job Ask Information About Itself

Question

I don't suppose anyone knows whether a SQL Server Agent Job can ask information about itself, such as its own ID, or the path it's running from? I'm aware of xp_sqlagent_enum_jobs and sp_help_job but this doesn't help, because you have to specify the job ID.

The idea is that we want code that we don't have to manage by being able to call a sproc which will identify the current job. Any ideas?

Answer 1

Yes, but it isn't pretty.

Look at the sys.sysprocesses (or dbo.sysprocesses in SQL 2000 and below). The program name will be SQL Agent something with a binary value at the end. That binary value is the binary vale of the guid of the job. So, substring out that value and do a lookup against the msdb.dbo.sysjobs table to find out what job it is (you'll need to cast the sysjobs.job_id to varbinary(100) to get the values to match).

I told you it wasn't pretty, but it will work.

Answer 2

nasty!!! but i think it might work...

eg. used within a job - select * from msdb..sysjobs where job_id = dbo.fn_currentJobId()

let me know.

create function dbo.fn_CurrentJobId()
returns uniqueidentifier
as
begin
declare @jobId uniqueidentifier

select @jobId = j.job_id
from master..sysprocesses s (nolock)
join msdb..sysjobs j (nolock)
on (j.job_id = SUBSTRING(s.program_name,38,2) + SUBSTRING(s.program_name,36,2) + SUBSTRING(s.program_name,34,2) + SUBSTRING(s.program_name,32,2) + '-' + SUBSTRING(s.program_name,42,2) + SUBSTRING(s.program_name,40,2) + '-' + SUBSTRING(s.program_name,46,2) + SUBSTRING(s.program_name,44,2) + '-' + SUBSTRING(s.program_name,48,4) + '-' + SUBSTRING(s.program_name,52,12) ) where s.spid = @@spid

return @jobId
end
go

thanks for the info though