January 13, 2017 at 10:05 am
Select top 100 C.name, C.Path, S.*
FROM
Catalog C
INNER JOIN Subscriptions S
on ( C.ItemID = S.Report_OID )
ORDER BY 1
How do I join the above with the Subscriptions table ?
January 13, 2017 at 11:11 am
I don't understand, the Subscriptions table is in the JOIN of the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2017 at 11:26 am
i have this saved from something i built off of an msdn, does this help?
left join ReportSchedule rs on Sch.ScheduleID = rs.ScheduleID
left join Subscriptions s on rs.SubscriptionID = s.SubscriptionID
left join [Catalog] rpt on s.Report_OID = rpt.ItemID
left join msdb.dbo.sysjobs b ON convert(varchar(40),rs.ScheduleID) = b.name
use ReportServer;
GO
IF OBJECT_ID('[dbo].[vwReportSchedules]') IS NOT NULL
DROP VIEW [dbo].[vwReportSchedules]
GO
--https://gallery.technet.microsoft.com/scriptcenter/SSRS-Schedule-in-Readable-2ec993a9
CREATE VIEW vwReportSchedules
AS
SELECT rpt.Path As ReportPath
,rpt.Name As ReportName
,Sch.Name As ScheduleName
,Sch.[Recurrence Type]
,Sch.[Recurrence Sub Type]
,Sch.[Run every (Hours)]
,Sch.[Runs every (Days)]
,Sch.[Runs every (weeks)]
,CASE
WHEN len(Sch.[Runs every (Week Days)]) > 0 THEN substring(Sch.[Runs every (Week Days)],1,len(Sch.[Runs every (Week Days)])-1)
ELSE ''
END AS [Runs every (Week Days)]
,Sch.[Runs every (Week of Month)]
,CASE
WHEN len(Sch.[Runs every (Month)]) > 0 THEN substring(Sch.[Runs every (Month)],1,len(Sch.[Runs every (Month)])-1)
ELSE ''
END AS [Runs every (Month)]
,CASE
WHEN len(Sch.[Runs every (Calendar Days)]) > 0 THEN substring(Sch.[Runs every (Calendar Days)],1,len(Sch.[Runs every (Calendar Days)])-1)
ELSE ''
END AS [Runs every (Calendar Days)]
,Sch.StartDate
,Sch.NextRunTime
,Sch.LastRunTime
,sch.EndDate
,b.job_id
,b.name As JobName
FROM
(
SELECT ScheduleID
,Name
,CASE
WHEN RecurrenceType=1 THEN 'Once'
WHEN RecurrenceType=2 THEN 'Hourly'
WHEN RecurrenceType=3 THEN 'Daily'
WHEN RecurrenceType=4 THEN 'Weekly'
WHEN RecurrenceType in (5,6) THEN 'Monthly'
END as 'Recurrence Type'
,CASE
WHEN RecurrenceType=1 THEN 'Once'
WHEN RecurrenceType=2 THEN 'Hourly'
WHEN RecurrenceType=3 THEN 'Daily'
WHEN RecurrenceType=4 and WeeksInterval <= 1 THEN 'Daily'
WHEN RecurrenceType=4 and WeeksInterval > 1 THEN 'Weekly'
WHEN RecurrenceType=5 THEN 'Calendar Daywise'
WHEN RecurrenceType=6 THEN 'WeekWise'
END
as 'Recurrence Sub Type'
,CASE RecurrenceType
WHEN 2 THEN CONVERT(varchar,MinutesInterval/60) + ' Hours(s) ' + CONVERT(varchar,MinutesInterval%60) + ' Minutes(s) '
ELSE ''
END as 'Run every (Hours)'
,ISNULL(CONVERT(VARCHAR(3),DaysInterval),'') as 'Runs every (Days)'
,ISNULL(CONVERT(VARCHAR(3),WeeksInterval),'') as 'Runs every (weeks)'
,CASE WHEN Daysofweek & POWER(2, 0) = POWER(2,0) THEN 'Sun,' ELSE '' END +
CASE WHEN Daysofweek & POWER(2, 1) = POWER(2,1) THEN 'Mon,' ELSE '' END +
CASE WHEN Daysofweek & POWER(2, 2) = POWER(2,2) THEN 'Tue,' ELSE '' END +
CASE WHEN Daysofweek & POWER(2, 3) = POWER(2,3) THEN 'Wed,' ELSE '' END +
CASE WHEN Daysofweek & POWER(2, 4) = POWER(2,4) THEN 'Thu,' ELSE '' END +
CASE WHEN Daysofweek & POWER(2, 5) = POWER(2,5) THEN 'Fri,' ELSE '' END +
CASE WHEN Daysofweek & POWER(2, 6) = POWER(2,6) THEN 'Sat,' ELSE '' END as 'Runs every (Week Days)'
,CASE
WHEN MonthlyWeek <= 4 THEN CONVERT(VARCHAR(2),MonthlyWeek )
WHEN MonthlyWeek = 5 THEN 'Last'
ELSE ''
END as 'Runs every (Week of Month)'
,CASE WHEN Month & POWER(2, 0) = POWER(2,0) THEN 'Jan,' ELSE '' END +
CASE WHEN Month & POWER(2, 1) = POWER(2,1) THEN 'Feb,' ELSE '' END +
CASE WHEN Month & POWER(2, 2) = POWER(2,2) THEN 'Mar,' ELSE '' END +
CASE WHEN Month & POWER(2, 3) = POWER(2,3) THEN 'Apr,' ELSE '' END +
CASE WHEN Month & POWER(2, 4) = POWER(2,4) THEN 'May,' ELSE '' END +
CASE WHEN Month & POWER(2, 5) = POWER(2,5) THEN 'Jun,' ELSE '' END +
CASE WHEN Month & POWER(2, 6) = POWER(2,6) THEN 'Jul,' ELSE '' END +
CASE WHEN Month & POWER(2, 7) = POWER(2,7) THEN 'Aug,' ELSE '' END +
CASE WHEN Month & POWER(2, 8) = POWER(2,8) THEN 'Sep,' ELSE '' END +
CASE WHEN Month & POWER(2, 9) = POWER(2,9) THEN 'Oct,' ELSE '' END +
CASE WHEN Month & POWER(2, 10) = POWER(2,10) THEN 'Nov,' ELSE '' END +
CASE WHEN Month & POWER(2, 11) = POWER(2,11) THEN 'Dec,' ELSE '' END as 'Runs every (Month)'
,CASE WHEN DaysOfMonth & POWER(2, 0) = POWER(2, 0) THEN '1,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 1) = POWER(2, 1) THEN '2,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 2) = POWER(2, 2) THEN '3,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 3) = POWER(2, 3) THEN '4,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 4) = POWER(2, 4) THEN '5,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 5) = POWER(2, 5) THEN '6,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 6) = POWER(2, 6) THEN '7,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 7) = POWER(2, 7) THEN '8,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 8) = POWER(2, 8) THEN '9,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 9) = POWER(2, 9) THEN '10,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 10) = POWER(2, 10) THEN '11,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 11) = POWER(2, 11) THEN '12,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 12) = POWER(2, 12) THEN '13,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 13) = POWER(2, 13) THEN '14,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 14) = POWER(2, 14) THEN '15,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 15) = POWER(2, 15) THEN '16,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 16) = POWER(2, 16) THEN '17,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 17) = POWER(2, 17) THEN '18,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 18) = POWER(2, 18) THEN '19,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 19) = POWER(2, 19) THEN '20,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 20) = POWER(2, 20) THEN '21,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 21) = POWER(2, 21) THEN '22,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 22) = POWER(2, 22) THEN '23,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 23) = POWER(2, 23) THEN '24,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 24) = POWER(2, 24) THEN '25,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 25) = POWER(2, 25) THEN '26,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 26) = POWER(2, 26) THEN '27,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 27) = POWER(2, 27) THEN '28,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 28) = POWER(2, 28) THEN '29,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 29) = POWER(2, 29) THEN '30,' ELSE '' END +
CASE WHEN DaysOfMonth & POWER(2, 30) = POWER(2, 30) THEN '31,' ELSE '' END as 'Runs every (Calendar Days)'
,StartDate
,NextRunTime
,LastRunTime
,EndDate
,Recurrencetype
FROM Schedule
) Sch
left join ReportSchedule rs on Sch.ScheduleID = rs.ScheduleID
left join Subscriptions s on rs.SubscriptionID = s.SubscriptionID
left join [Catalog] rpt on s.Report_OID = rpt.ItemID
left join msdb.dbo.sysjobs b ON convert(varchar(40),rs.ScheduleID) = b.name
--ORDER BY Recurrencetype
Lowell
January 13, 2017 at 2:04 pm
mw112009 - Friday, January 13, 2017 10:05 AM
Select top 100 C.name, C.Path, S.*
FROM
Catalog C
INNER JOIN Subscriptions S
on ( C.ItemID = S.Report_OID )
ORDER BY 1
How do I join the above with the Subscriptions table ?
My mistake... Sorry I should have said.. How to join with SCHEDULE
January 13, 2017 at 2:33 pm
mw112009 - Friday, January 13, 2017 2:04 PMmw112009 - Friday, January 13, 2017 10:05 AM
Select top 100 C.name, C.Path, S.*
FROM
Catalog C
INNER JOIN Subscriptions S
on ( C.ItemID = S.Report_OID )
ORDER BY 1
How do I join the above with the Subscriptions table ?My mistake... Sorry I should have said.. How to join with SCHEDULE
Thanks for the joins shared by LOWELL... That works...
NEXT QUESTION:
My real issue is this
1. I am moving reports from a 2008 ReportServer to a 2012 ReportServer
2. The reports moved just fine ( via VISUAL STUDIO 2015 ). Worked well
3. Now the challenge is to move the SUBSCRIPTIONS to the 2012 Server.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply