How to check if running store procedure was triggered by job?

Question

Is there a way to check within store procedure whether it was triggered by Job or using other methods i.e. SSIS, at hoc query etc?

There was similar question SQL Server Find What Jobs Are Running a Procedure but it just check job steps, but this is not what I need.

This is my scenario.

I have SP spDoSomethingImportant and this can be run by Job or i.e. user in database.

So within that SP I want to have a check if it was trigger by a job and get the name or Id of that job.

You can't do that as you are asking. But you can determine which user the connection was made from. Assuming you have a dedicated jobs user it should be pretty straight forward. — Sean Lange, Feb 05 '16 at 14:05
Thank you for your comment. Unfortunately, there can be number of users that can run this SP directly, or it will be run on schedule (by Sql Agent - service account) so I will not be able to do it I'm afraid. — Wojciech Jakubas, Feb 05 '16 at 14:10
Yeah I don't think so. Remember that sql server doesn't care about your jobs. All they really do is make a nice package that executes sql just like any other user. As such you can't tell what job invoked a step unless you put that information in the connection string. ApplicationName, UserName etc to help identify what is calling your procedures. — Sean Lange, Feb 05 '16 at 14:12
Yes, I thought so. I guess I will have to add something like parameter where I can pass the job ID or name or something else that will help me trace what triggered given store procedure. Thanks — Wojciech Jakubas, Feb 05 '16 at 14:16
I wonder if you could use [CONTEXT_INFO](https://technet.microsoft.com/en-us/library/ms189252(v=sql.105).aspx) to help identify whether it came from fixed callers (agent, ssis, etc) and then by the absence of it being set, we can assume it's ad hoc? — billinkc, Feb 05 '16 at 14:39
If you can edit the stored procedure, maybe could you give it a [default = NULL] parameter that takes the `USER_NAME()` if null and then either return the proxy that ran the package, or alter the package to give a specific value such as "SSISpackageName"? It's a bit long winded but might give the info you need. — High Plains Grifter, Feb 05 '16 at 15:04

score 3 · Accepted Answer · answered Feb 05 '16 at 15:30

I created the following SPROC:

CREATE PROCEDURE TESTME
AS

INSERT  INTO TEST_LOG
SELECT  *
FROM    sys.dm_exec_sessions
WHERE   session_Id = @@SPID

GO

I then ran it from SSMS. In TEST_LOG, the column Program_name is "Microsoft SQL Server Management Studio - Query"

I then created a job to run this SPROC. In TEST_LOG, the column Program_name is "SQLAgent - TSQL JobStep (Job 0xCB393E8FF0E9D44485204D0100803469 : Step 1)"

So if you didn't want to pass a parameter to indicate if it's running as a job, I think you could figure it out from sys.dm_exec_sessions.Program_name

This might not be ideal solution and it may not work for everything but it should work for Job names. Thank you. — Wojciech Jakubas, Feb 05 '16 at 18:00

score 0 · Answer 2 · answered Feb 05 '16 at 17:55

Following great advice from Robert Paulsen I have created simple solution to achieve this.

I have also used some code from this location to convert string to unique identifier. It was posted by Scott Pletcher. There is a function to get this uid from string. http://www.sqlservercentral.com/Forums/FindPost1480606.aspx

Example code can look like this. There is a need for more checks as the code will not work if is not run by SQL Agent.

--Get Program Name
DECLARE @ProgramName VARCHAR(255)
DECLARE @StartOfJjobId INT
DECLARE @JobIdPrefix VARCHAR(30) = '(Job 0x'
SET @ProgramName = (SELECT Program_name FROM sys.dm_exec_sessions WHERE session_Id = @@SPID)
--If run by job it will returns something like this line below
--SQLAgent - TSQL JobStep (Job 0x5A9C063C3BDE5D41B4CBF86D4C1A82A5 Step 1)
SET @StartOfJjobId = CHARINDEX(@JobIdPrefix, @ProgramName) + LEN(@JobIdPrefix)

select name AS JobName from msdb..sysjobs where job_id = (SELECT CAST(
        SUBSTRING(@ProgramName, @StartOfJjobId + 06, 2) + SUBSTRING(@ProgramName, @StartOfJjobId + 04, 2) + 
        SUBSTRING(@ProgramName, @StartOfJjobId + 02, 2) + SUBSTRING(@ProgramName, @StartOfJjobId + 00, 2) + '-' +
        SUBSTRING(@ProgramName, @StartOfJjobId + 10, 2) + SUBSTRING(@ProgramName, @StartOfJjobId + 08, 2) + '-' +
        SUBSTRING(@ProgramName, @StartOfJjobId + 14, 2) + SUBSTRING(@ProgramName, @StartOfJjobId + 12, 2) + '-' +
        SUBSTRING(@ProgramName, @StartOfJjobId + 16, 4) + '-' +
        SUBSTRING(@ProgramName, @StartOfJjobId + 20,12) AS uniqueidentifier))

How to check if running store procedure was triggered by job?

2 Answers2