I have T-SQL code which sends customized emails as final step in SQL Server Agent jobs. It's working just fine if I specify job_id or job_name.
I would like somehow to automatically retrieve job_id or job_name under which job was executed as part of T-SQL.
Yeah, your solution is what I need but on the moment I want to save it as stored procedure following message appears:
"Conversion failed when converting from a character string to uniqueidentifier. [SQLSTATE 42000] (Error 8169)."
Running SQL Agent job with T-SQL code works instead.
Pass it to SProc as a VARCHAR @Parameter, store it (or output it) somewhere so you can see how it is formatted. Might have "{" ... "}" (although SQL normally parses them, if present, without any error). Probably something else goofy in the way the GUID is formatted / presented as a parameter to the Sproc
DECLARE @strJobID varchar(1000)
SET @strJobID = $(ESCAPE_NONE(JOBID))
RAISERROR('@strJobID ]%s[',0,1, @strJobID) WITH LOG, NOWAIT
-- Check SQL Error Log to see the exact value, if it is not displayed, visibly, on the screen
EXEC [dbo].[MySP] @JobID=@strJobID -- Implicit conversion