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? – Mark Wilkinson Jul 29 '14 at 10:18
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 '14 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 '14 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 '14 at 14:37
  • owned by NT AUTHORITY\NETWORK SERVICE – Peter Jul 29 '14 at 14:37
  • Ok so anyone got any ideas? Is this a bug or something I can resolve? – Peter Jul 30 '14 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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