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(program_name,30,34)).

Mark

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

Find duplicates
Selecting when Dates in where clause
Check if files exist Stored Proc
Text datatype accepts only 8000 characters
xp_sendmail