Scheduled Jobs Friendly Names?

Is there a way I can list the friendly names for jobs scheduled through the
SQL Server Agent/Jobs?
We are trying to get a list of what the jobs are that run at specified times
for our Disaster Recovery, and the Reporting Services Jobs are listed by
their GUID only.
thanks,
-smossThe Catalog, Subscriptions, ReportSchedule and Schedule tables in the RS
database should give you that information that you need. Catalog will give
you the name of the report, Subscriptions will give you the name of the
subscription, Schedule gives you the last run information, and ReportSchedule
gives you the scheduling information. Just create a view linking them to get
the list that you need.
Hope that helps!
"smoss" wrote:
> Is there a way I can list the friendly names for jobs scheduled through the
> SQL Server Agent/Jobs?
> We are trying to get a list of what the jobs are that run at specified times
> for our Disaster Recovery, and the Reporting Services Jobs are listed by
> their GUID only.
> thanks,
> -smoss|||So I should be able to hat you could join from catalog to subscription to
reportschedule.. but there's no obvious key relationship based on the field
names.. they're named different depending on the table.
Can you advise how the tables are related?
thanks!
-smoss
"daw" wrote:
> The Catalog, Subscriptions, ReportSchedule and Schedule tables in the RS
> database should give you that information that you need. Catalog will give
> you the name of the report, Subscriptions will give you the name of the
> subscription, Schedule gives you the last run information, and ReportSchedule
> gives you the scheduling information. Just create a view linking them to get
> the list that you need.
> Hope that helps!
> "smoss" wrote:
> > Is there a way I can list the friendly names for jobs scheduled through the
> > SQL Server Agent/Jobs?
> >
> > We are trying to get a list of what the jobs are that run at specified times
> > for our Disaster Recovery, and the Reporting Services Jobs are listed by
> > their GUID only.
> >
> > thanks,
> > -smoss|||This is the view that I created to list the last subscription run:
SELECT dbo.[Catalog].Path, dbo.[Catalog].Name AS ReportName,
dbo.Subscriptions.Description AS SubscriptDesc,
dbo.Subscriptions.LastRunTime,
dbo.Subscriptions.LastStatus
FROM dbo.Subscriptions INNER JOIN
dbo.ReportSchedule ON dbo.Subscriptions.SubscriptionID
= dbo.ReportSchedule.SubscriptionID INNER JOIN
dbo.Schedule ON dbo.ReportSchedule.ScheduleID =dbo.Schedule.ScheduleID INNER JOIN
dbo.[Catalog] ON dbo.Subscriptions.Report_OID =dbo.[Catalog].ItemID AND dbo.ReportSchedule.ReportID = dbo.[Catalog].ItemID
"smoss" wrote:
> So I should be able to hat you could join from catalog to subscription to
> reportschedule.. but there's no obvious key relationship based on the field
> names.. they're named different depending on the table.
> Can you advise how the tables are related?
> thanks!
> -smoss
>
> "daw" wrote:
> > The Catalog, Subscriptions, ReportSchedule and Schedule tables in the RS
> > database should give you that information that you need. Catalog will give
> > you the name of the report, Subscriptions will give you the name of the
> > subscription, Schedule gives you the last run information, and ReportSchedule
> > gives you the scheduling information. Just create a view linking them to get
> > the list that you need.
> > Hope that helps!
> >
> > "smoss" wrote:
> >
> > > Is there a way I can list the friendly names for jobs scheduled through the
> > > SQL Server Agent/Jobs?
> > >
> > > We are trying to get a list of what the jobs are that run at specified times
> > > for our Disaster Recovery, and the Reporting Services Jobs are listed by
> > > their GUID only.
> > >
> > > thanks,
> > > -smoss|||Perfect. thanks!
"daw" wrote:
> This is the view that I created to list the last subscription run:
> SELECT dbo.[Catalog].Path, dbo.[Catalog].Name AS ReportName,
> dbo.Subscriptions.Description AS SubscriptDesc,
> dbo.Subscriptions.LastRunTime,
> dbo.Subscriptions.LastStatus
> FROM dbo.Subscriptions INNER JOIN
> dbo.ReportSchedule ON dbo.Subscriptions.SubscriptionID
> = dbo.ReportSchedule.SubscriptionID INNER JOIN
> dbo.Schedule ON dbo.ReportSchedule.ScheduleID => dbo.Schedule.ScheduleID INNER JOIN
> dbo.[Catalog] ON dbo.Subscriptions.Report_OID => dbo.[Catalog].ItemID AND dbo.ReportSchedule.ReportID = dbo.[Catalog].ItemID
> "smoss" wrote:
> > So I should be able to hat you could join from catalog to subscription to
> > reportschedule.. but there's no obvious key relationship based on the field
> > names.. they're named different depending on the table.
> >
> > Can you advise how the tables are related?
> >
> > thanks!
> > -smoss
> >
> >
> > "daw" wrote:
> >
> > > The Catalog, Subscriptions, ReportSchedule and Schedule tables in the RS
> > > database should give you that information that you need. Catalog will give
> > > you the name of the report, Subscriptions will give you the name of the
> > > subscription, Schedule gives you the last run information, and ReportSchedule
> > > gives you the scheduling information. Just create a view linking them to get
> > > the list that you need.
> > > Hope that helps!
> > >
> > > "smoss" wrote:
> > >
> > > > Is there a way I can list the friendly names for jobs scheduled through the
> > > > SQL Server Agent/Jobs?
> > > >
> > > > We are trying to get a list of what the jobs are that run at specified times
> > > > for our Disaster Recovery, and the Reporting Services Jobs are listed by
> > > > their GUID only.
> > > >
> > > > thanks,
> > > > -smoss|||Here is another query you can use that I think is more useful, but still
needs some more entries to the CASE statements:
select
cat.name as 'ReportName',
rs.scheduleid as 'SQLJobID',
sub.description as 'JobAlias',
case s.recurrencetype
when 1 then
'Once'
when 3 then
case s.daysinterval
when 1 then
'Every day'
else
'Every other ' + cast(s.daysinterval as varchar) + ' day.'
end
when 4 then
case s.daysofweek
when 2 then
'Every ' + cast(s.weeksinterval as varchar) + ' week on Mondays'
when 42 then
'Every ' + cast(s.weeksinterval as varchar) + ' week on Mondays,
Wednesdays, and Fridays'
when 62 then
'Every ' + cast(s.weeksinterval as varchar) + ' week on Mondays,
Tuesdays, Wednesdays, Thursdays and Fridays'
when 127 then
'Every ' + cast(s.weeksinterval as varchar) + ' week on every day'
end
when 5 then
'Day ' + cast(s.daysofmonth as varchar) + ' of each month'
when 6 then
'The ' +
case s.monthlyweek
when 1 then
'first'
when 2 then
'second'
when 3 then
'third'
when 4 then
'fourth'
when 5 then
'last'
else
'UKNOWN'
end + ' week of each month on ' +
case s.daysofweek
when 2 then
'Monday'
else
'Unknown'
end
else
'Unknown'
end + ' at ' + ltrim(right(convert(varchar,s.startdate,100),7)) as
'ScheduleDetails',
s.startdate as 'Start',
s.lastruntime as 'LastExec',
s.recurrencetype as 'RecurrenceType',
s.minutesinterval as 'MinsInterval',
s.daysinterval as 'DaysInterval',
s.weeksinterval as 'WeeksInterval',
s.daysofweek as 'DaysOfWeek',
s.daysofmonth as 'DaysOfMonth',
s.monthlyweek as 'MonthlyWeek',
cat.path as 'ReportPath'
from
catalog cat join reportschedule rs on cat.itemid = rs.reportid
join schedule s on rs.scheduleid = s.scheduleid
join subscriptions sub on sub.subscriptioniD = rs.subscriptioniD
where
len(cat.name) > 0
order by
cat.name
"smoss" <smoss@.discussions.microsoft.com> wrote in message
news:27B36A4B-43D7-4EC7-9989-1DB612C2B6F4@.microsoft.com...
> Perfect. thanks!
> "daw" wrote:
>> This is the view that I created to list the last subscription run:
>> SELECT dbo.[Catalog].Path, dbo.[Catalog].Name AS ReportName,
>> dbo.Subscriptions.Description AS SubscriptDesc,
>> dbo.Subscriptions.LastRunTime,
>> dbo.Subscriptions.LastStatus
>> FROM dbo.Subscriptions INNER JOIN
>> dbo.ReportSchedule ON
>> dbo.Subscriptions.SubscriptionID
>> = dbo.ReportSchedule.SubscriptionID INNER JOIN
>> dbo.Schedule ON dbo.ReportSchedule.ScheduleID =>> dbo.Schedule.ScheduleID INNER JOIN
>> dbo.[Catalog] ON dbo.Subscriptions.Report_OID =>> dbo.[Catalog].ItemID AND dbo.ReportSchedule.ReportID =>> dbo.[Catalog].ItemID
>> "smoss" wrote:
>> > So I should be able to hat you could join from catalog to subscription
>> > to
>> > reportschedule.. but there's no obvious key relationship based on the
>> > field
>> > names.. they're named different depending on the table.
>> >
>> > Can you advise how the tables are related?
>> >
>> > thanks!
>> > -smoss
>> >
>> >
>> > "daw" wrote:
>> >
>> > > The Catalog, Subscriptions, ReportSchedule and Schedule tables in the
>> > > RS
>> > > database should give you that information that you need. Catalog
>> > > will give
>> > > you the name of the report, Subscriptions will give you the name of
>> > > the
>> > > subscription, Schedule gives you the last run information, and
>> > > ReportSchedule
>> > > gives you the scheduling information. Just create a view linking
>> > > them to get
>> > > the list that you need.
>> > > Hope that helps!
>> > >
>> > > "smoss" wrote:
>> > >
>> > > > Is there a way I can list the friendly names for jobs scheduled
>> > > > through the
>> > > > SQL Server Agent/Jobs?
>> > > >
>> > > > We are trying to get a list of what the jobs are that run at
>> > > > specified times
>> > > > for our Disaster Recovery, and the Reporting Services Jobs are
>> > > > listed by
>> > > > their GUID only.
>> > > >
>> > > > thanks,
>> > > > -smoss|||Here's another query you can try, but it still needs some tweaking on the
case statements:
select
cat.name as 'ReportName',
rs.scheduleid as 'SQLJobID',
sub.description as 'JobAlias',
case s.recurrencetype
when 1 then
'Once'
when 3 then
case s.daysinterval
when 1 then
'Every day'
else
'Every other ' + cast(s.daysinterval as varchar) + ' day.'
end
when 4 then
case s.daysofweek
when 2 then
'Every ' + cast(s.weeksinterval as varchar) + ' week on Mondays'
when 42 then
'Every ' + cast(s.weeksinterval as varchar) + ' week on Mondays,
Wednesdays, and Fridays'
when 62 then
'Every ' + cast(s.weeksinterval as varchar) + ' week on Mondays,
Tuesdays, Wednesdays, Thursdays and Fridays'
when 127 then
'Every ' + cast(s.weeksinterval as varchar) + ' week on every day'
end
when 5 then
'Day ' + cast(s.daysofmonth as varchar) + ' of each month'
when 6 then
'The ' +
case s.monthlyweek
when 1 then
'first'
when 2 then
'second'
when 3 then
'third'
when 4 then
'fourth'
when 5 then
'last'
else
'UKNOWN'
end + ' week of each month on ' +
case s.daysofweek
when 2 then
'Monday'
else
'Unknown'
end
else
'Unknown'
end + ' at ' + ltrim(right(convert(varchar,s.startdate,100),7)) as
'ScheduleDetails',
s.startdate as 'Start',
s.lastruntime as 'LastExec',
s.recurrencetype as 'RecurrenceType',
s.minutesinterval as 'MinsInterval',
s.daysinterval as 'DaysInterval',
s.weeksinterval as 'WeeksInterval',
s.daysofweek as 'DaysOfWeek',
s.daysofmonth as 'DaysOfMonth',
s.monthlyweek as 'MonthlyWeek',
cat.path as 'ReportPath'
from
catalog cat join reportschedule rs on cat.itemid = rs.reportid
join schedule s on rs.scheduleid = s.scheduleid
join subscriptions sub on sub.subscriptioniD = rs.subscriptioniD
where
len(cat.name) > 0
order by
cat.name
"smoss" <smoss@.discussions.microsoft.com> wrote in message
news:27B36A4B-43D7-4EC7-9989-1DB612C2B6F4@.microsoft.com...
> Perfect. thanks!
> "daw" wrote:
>> This is the view that I created to list the last subscription run:
>> SELECT dbo.[Catalog].Path, dbo.[Catalog].Name AS ReportName,
>> dbo.Subscriptions.Description AS SubscriptDesc,
>> dbo.Subscriptions.LastRunTime,
>> dbo.Subscriptions.LastStatus
>> FROM dbo.Subscriptions INNER JOIN
>> dbo.ReportSchedule ON
>> dbo.Subscriptions.SubscriptionID
>> = dbo.ReportSchedule.SubscriptionID INNER JOIN
>> dbo.Schedule ON dbo.ReportSchedule.ScheduleID =>> dbo.Schedule.ScheduleID INNER JOIN
>> dbo.[Catalog] ON dbo.Subscriptions.Report_OID =>> dbo.[Catalog].ItemID AND dbo.ReportSchedule.ReportID =>> dbo.[Catalog].ItemID
>> "smoss" wrote:
>> > So I should be able to hat you could join from catalog to subscription
>> > to
>> > reportschedule.. but there's no obvious key relationship based on the
>> > field
>> > names.. they're named different depending on the table.
>> >
>> > Can you advise how the tables are related?
>> >
>> > thanks!
>> > -smoss
>> >
>> >
>> > "daw" wrote:
>> >
>> > > The Catalog, Subscriptions, ReportSchedule and Schedule tables in the
>> > > RS
>> > > database should give you that information that you need. Catalog
>> > > will give
>> > > you the name of the report, Subscriptions will give you the name of
>> > > the
>> > > subscription, Schedule gives you the last run information, and
>> > > ReportSchedule
>> > > gives you the scheduling information. Just create a view linking
>> > > them to get
>> > > the list that you need.
>> > > Hope that helps!
>> > >
>> > > "smoss" wrote:
>> > >
>> > > > Is there a way I can list the friendly names for jobs scheduled
>> > > > through the
>> > > > SQL Server Agent/Jobs?
>> > > >
>> > > > We are trying to get a list of what the jobs are that run at
>> > > > specified times
>> > > > for our Disaster Recovery, and the Reporting Services Jobs are
>> > > > listed by
>> > > > their GUID only.
>> > > >
>> > > > thanks,
>> > > > -smoss