Thinking outside the box

Patron Saint of Lost Yaks
posts - 202, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories
<< Keep track of all your jobs schedules | Home | Delete all subset records >>

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

Print | posted on Monday, October 13, 2008 10:32 AM | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# 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.
10/16/2008 5:41 AM | Dean
Gravatar

# 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
10/16/2008 8:08 AM | Peso
Gravatar

# re: Get the job name for current context

Great work...
12/9/2009 9:59 AM | AR
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET

Copyright © Peter Larsson