Convert varchar to uniqueidentifier

When I need to have more info about a process, I run this kind of query:
SELECT PROGRAM_NAME FROM master..sysprocesses WHERE program_name LIKE
'SQLAgent - TSQL JobStep (Job %'
The result might be something like this:
SQLAgent - TSQL JobStep (Job 0x9BD0927CE9086241B582AAAAD7D3B86D : Step
1)
When I want to have more info about that particular job, I run this
query:
SELECT * FROM msdb..sysjobs WHERE job_id =
0x9BD0927CE9086241B582AAAAD7D3B86D
Now how do I combine these two queries? The first version you might
suggest is:
SELECT *
FROM msdb..sysjobs
WHERE job_id IN
(SELECT SUBSTRING(program_name,30,34)
FROM master..sysprocesses
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
This gives the result
Syntax error converting from a character string to uniqueidentifier.
Can you help me with this? How to convert the result of the subquery to
uniqueidentifier? I didn't succeed to use the usual convert function
CONVERT(UNIQUEIDENTIFIER,SUBSTRING(progr
am_name,30,34)).
MarkTry this (untested):
SELECT *
FROM msdb..sysjobs
WHERE cast(job_id as nvarchar(128)) IN
(SELECT cast(SUBSTRING(program_name,30,34) as nvarchar(128))
FROM master..sysprocesses
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
ML|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:9FB96207-78A4-4566-9AEC-8294830AABAB@.microsoft.com...
> Try this (untested):
> SELECT *
> FROM msdb..sysjobs
> WHERE cast(job_id as nvarchar(128)) IN
> (SELECT cast(SUBSTRING(program_name,30,34) as nvarchar(128))
> FROM master..sysprocesses
> WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
>
> ML
Thanks, but that doesn't work either - I tested. But at least that doesn't
give any errors, just an empty result.
However, I found out, that this works. Quite ugly one, but it works.
SELECT *
FROM msdb..sysjobs
WHERE job_id IN
(SELECT SUBSTRING(program_name,38,2) +
SUBSTRING(program_name,36,2) +
SUBSTRING(program_name,34,2) +
SUBSTRING(program_name,32,2) + '-' +
SUBSTRING(program_name,42,2) +
SUBSTRING(program_name,40,2) + '-' +
SUBSTRING(program_name,46,2) +
SUBSTRING(program_name,44,2) + '-' +
SUBSTRING(program_name,48,4) + '-' +
SUBSTRING(program_name,52,12)
FROM master..sysprocesses
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
Mark