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:

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

1
  • 1
    Do you use subscriptions in reporting services? Jul 29, 2014 at 10:18

2 Answers 2

3

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
1

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
6
  • Hi Shanky, Interestingly, that query returns nothing when I use the hex id of any of the jobs listed in the SQL Agent jobs node. If I use any of the hex ids from the sysjobs dmv I also return no results.
    – Peter
    Jul 29, 2014 at 10:09
  • Can you give more information about these Hex jobs schedule, owner properties this would help in drilling down issue
    – Shanky
    Jul 29, 2014 at 12:35
  • Looks like SSRS subscriptions: This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.
    – Peter
    Jul 29, 2014 at 14:37
  • owned by NT AUTHORITY\NETWORK SERVICE
    – Peter
    Jul 29, 2014 at 14:37
  • Ok so anyone got any ideas? Is this a bug or something I can resolve?
    – Peter
    Jul 30, 2014 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.