SQL agent jobs appear as job id only

Question

SQL Server 2005, I'm seeing about 100 'jobs' in the SQL Agent>jobs node in SSMS. They appear as hexadecimal strings not with their real names:

If I query msdb..sysjobs, I see a much smaller list of jobs and their corresponding JobName.

So what are these jobs and how can I make them appear as their real job name as opposed to the JobID?

Thanks, Peter

Answer 1

SQL Server 2005, I'm seeing about 100 'jobs' in the SQL Agent>jobs node in SSMS. They appear as hexadecimal strings not with their real names:

They are from reporting service subscriptions.

Below SQL will give you list of the job names against the actual reports

select ScheduleID
    ,Path
    ,name
    ,s.Description
from ReportServer.dbo.catalog c
inner join ReportServer.dbo.Subscriptions s on c.ItemID = s.Report_OID
inner join ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID

Even better (got it from this link):

A SQL Server Agent job is created to control the scheduled execution of the report, and this is stored in the sysjobs and sysjobsteps of the MSDB database. The agent job name is a guid value that represents the ScheduleId (yes, in case you've not yet noticed, this makes your Agent Job list messy with a bunch of guids that make it hard to work with your non-subscription jobs, we are hearing you:).

select 'Report' = c.Path
    ,'Subscription' = s.Description
    ,'SubscriptionOwner' = uo.UserName
    ,'SubscriptionModBy' = um.UserName
    ,'SubscriptionModDate' = s.ModifiedDate
    ,'ProcessStart' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.ProcessStart)
    ,'NotificationEntered' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.NotificationEntered)
    ,'ProcessAfter' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.ProcessAfter)
    ,n.Attempt
    ,'SubscriptionLastRunTime' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.SubscriptionLastRunTime)
    ,n.IsDataDriven
    ,'ProcessHeartbeat' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.ProcessHeartbeat)
    ,n.Version
    ,n.SubscriptionID
from Notifications n
inner join Subscriptions s on n.SubscriptionID = s.SubscriptionID
inner join catalog c on c.ItemID = n.ReportID
inner join Users uo on uo.UserID = s.OwnerID
inner join Users um on um.UserID = s.ModifiedByID

Answer 2

That hexadecimal job name must have come from job_id column which is present in both msdb.dbo.sysjobhistory table and msdb.dbo.sysjobs table. What does below query returns for the hex job ID

select sj.name,
sj.description,
sjh.step_name
from 
dbo.sysjobhistory sjh
join
dbo.sysjobs sj
on 
sj.job_id=sjh.job_id
where sjh.job_id=''--hex value