I have a common stored procedure that is called from any number of Jobs and
I'm looking to determine if theres any way that the stored proc using SQL 
can determine the ID of the job which executed it so I  can go out to
MSDB..sysjobs and get the job name and insert it into any error messages that
might come out.

You could add a parameter to the SP, and code it into each job.

I assume you have a logging table or something like it?  For simplicity:

CREATE TABLE joblogging
(
jobname SYSNAME,
dt SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
)
GO

Now add this insert to the stored procedure:

ALTER PROCEDURE dbo.yourproc
@jobID UNIQUEIDENTIFIER
AS
BEGIN
/* other stuff */

INSERT joblogging(jobname)
SELECT name
FROM msdb..sysjobs
WHERE job_id = @jobID

END
GO

And then you can use the [JOBID] token in each job:

EXEC yourproc @jobid='[JOBID]'

You can also include the [STEPID] and other variables; see sp_add_jobstep in
Books Online for a complete list.

Yes that's a pain in the butt, but how many jobs are really calling the
exact same stored procedure?




Show quoteHide quote

[OT] SQL Humor
composite key structure
Default Column Values
Finding similar sales
return set of only first unique row