SQLTeam.com | Weblogs | Forums

Solution to retreive job_id/job_name


#1

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?


#2

This maybe?

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

#3

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.


#4

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


#5

I wrote syntax below in SQL Agent:

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

Thank you!


#6

I would be inclined to do:

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

#7

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


#8

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


#9

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