SQLTeam.com | Weblogs | Forums

Solution to retreive job_id/job_name

Hi guys.

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.

It is possible?

This maybe?

CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))

Hi Kristen.

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

I wrote syntax below in SQL Agent:

SET @JobID = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))
EXEC [dbo].[MySP] @JobID

Thank you!

I would be inclined to do:

DECLARE @strJobID varchar(1000)
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

[SQLSTATE 01000] (Message 50000) Error converting data type varchar to uniqueidentifier. [SQLSTATE 42000] (Error 8114)

I suggest removing / commenting-out the EXEC for now, then have a look in the logs to see what the logging of the GUID looks like

Kristen, something like this is displaying: @strJobID ]Òã¤<…vE¶¶u|A6{[
So...what am I missing here?