Get the job name for current context
Today I had to write some code to dynamically get the job name currently running.
DECLARE @SQL NVARCHAR(72),
@jobID UNIQUEIDENTIFIER,
@jobName SYSNAME
SET @SQL = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)'
EXEC sp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @guid = @jobID OUT
SELECT @jobName = name
FROM msdb..sysjobs
WHERE job_id = @jobID
@jobID UNIQUEIDENTIFIER,
@jobName SYSNAME
SET @SQL = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)'
EXEC sp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @guid = @jobID OUT
SELECT @jobName = name
FROM msdb..sysjobs
WHERE job_id = @jobID
Legacy Comments
|
Dean
2008-10-16 |
re: Get the job name for current context Interesting code. Can it not be simplified to just (untested): SELECT @job_name = name FROM msdb..sysjobs WHERE job_id = CAST(APP_NAME() AS UNIQUE_IDENTIFIER) ? It might need the substring on APP_NAME() still, but I don't have anything here to test it. |
|
Peso
2008-10-16 |
re: Get the job name for current context No. You can't cast varchar data to uniqueidentifier "Conversion failed when converting from a character string to uniqueidentifier." Also, the string provided by APP_NAME() is a binary representation of the uniqueidentifier. The binary vaue of 0x1F62618385BECB408A0F0A9FB389FB79 must be converted into a uniqueidentifier as this 8361621F-BE85-40CB-8A0F-0A9FB389FB79 |
|
AR
2009-12-09 |
re: Get the job name for current context Great work... |