How to convert uniqueidentifier from string to use in a join / where clause

Question

How to convert uniqueidentifier from string to use in a join / where clause

DennisPost

SSCrazy

Points: 2691
More actions
January 6, 2015 at 5:15 am

#302188

Hi,
I am wanting to get the job name based on sys.sysProcesses.[Program_name] column.
Why is this query not returning any results even though the 2nd substringed guids are found the the sysJobs table?
SELECTCASE
WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN J.Name
ELSE RTRIM([program_name])
END ProgramName
, Val1.UqID
, Val1.UqIDStr
FROMsys.sysProcesses P
CROSS APPLY
(
VALUES(
CASE
WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN
CAST(
SUBSTRING(RTRIM([program_name]), 32, 8) + '-' +
SUBSTRING(RTRIM([program_name]), 40, 4) + '-' +
SUBSTRING(RTRIM([program_name]), 44, 4) + '-' +
SUBSTRING(RTRIM([program_name]), 48, 4) + '-' +
SUBSTRING(RTRIM([program_name]), 52, 12)
AS uniqueidentifier)
ELSE NULL
END ,
CASE
WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN
'0x' + SUBSTRING(RTRIM([program_name]), 32, 32)
ELSE NULL
END
)
) Val1(UqID, UqIDStr)
OUTER APPLY
(
SELECTDISTINCT Name
FROMMSDB.dbo.SysJobs
WHERECAST(Job_ID AS varchar(50)) = Val1.UqIDStr
OR Job_ID = Val1.UqID
OR CAST(Job_ID AS varchar(50)) = Val1.UqID
) J
WHERERTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %'
-- Copying the Val1.UqIDStr over the GUID and running the query, returns the correct results.
SELECTJob_ID, Name
FROMMSDB.dbo.sysJobs
WHEREJob_ID = 0xE8E91E18C358B84389AE94C86EFCE68B
Cheers

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Dennis, I had a similar frustrating struggle a couple of weeks ago, tying up trace data with jobs. This is what I came up with, more or less:

-- Agent jobs list

IF OBJECT_ID ('tempdb..#Jobs') IS NOT NULL DROP TABLE #Jobs

SELECT

JobName = Name + ': ' + REPLACE([Description],'No description available.',''),

job_id = master.dbo.fn_varbintohexstr(CAST(job_id AS VARBINARY(32)))

INTO #Jobs

FROM msdb.dbo.sysjobs

SELECT

ApplicationName = CASE WHEN j.job_id IS NULL THEN t.ApplicationName ELSE 'Agent: '+j.JobName + ' ('+LTRIM(RIGHT(t.ApplicationName,8)) END,

t.ApplicationName,

j.JobName,

RIGHT(t.ApplicationName,8),

j.job_id,

SUBSTRING(t.ApplicationName,30,34)

FROM fn_trace_gettable(N'D:\Perflogs\AllQueries20150104090517.trc',DEFAULT) t

inner JOIN #Jobs j ON j.job_id = SUBSTRING(t.ApplicationName,30,34)

Columns 2-6 show you how it works.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

Change

'0x' + SUBSTRING(RTRIM([program_name]), 32, 32)

to

CAST(CONVERT(VARBINARY(MAX),'0x' + SUBSTRING(RTRIM([program_name]), 32, 32),1) AS uniqueidentifier)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

DennisPost SSCrazy Points: 2691 More actions · Answer 3

Hi Chris,

Thanks for taking the time to post a reply. 🙂

What's the magic going on behind master.dbo.fn_varbintohexstr? Not just a CAST or a CONVERT I think.

DennisPost SSCrazy Points: 2691 More actions · Answer 4

Mark Cowne (1/6/2015)
Change
'0x' + SUBSTRING(RTRIM([program_name]), 32, 32)
to
CAST(CONVERT(VARBINARY(MAX),'0x' + SUBSTRING(RTRIM([program_name]), 32, 32),1) AS uniqueidentifier)

Thanks Mark, that did the trick.

Here's what BOL says about using CONVERT with VARBINARY

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

DennisPost (1/6/2015)
Mark Cowne (1/6/2015)
Change
'0x' + SUBSTRING(RTRIM([program_name]), 32, 32)
to
CAST(CONVERT(VARBINARY(MAX),'0x' + SUBSTRING(RTRIM([program_name]), 32, 32),1) AS uniqueidentifier)
Thanks Mark, that did the trick.
Here's what BOL says about using CONVERT with VARBINARY

You're welcome.