ReportServer - Unable to SELECT FROM VIEW - See attached - Why Am I getting this error ?
-
Why am I getting this error ( in the picture )
Help me troubleshoot
USE [ReportServer]
GO/****** Object: View [dbo].[vwReportSchedules] Script Date: 1/18/2017 2:17:02 PM ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO--https://gallery.technet.microsoft.com/scriptcenter/SSRS-Schedule-in-Readable-2ec993a9
CREATE VIEW [dbo].[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 JobNameFROM
(
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 RecurrencetypeGO
-
You will need to look at the permissions of the user that the report is running under. Not sure how to recreate the error on my own system, so not much more I can do at the moment.<
- Lynn Pettis - Wednesday, January 18, 2017 12:24 PM
Lynn I am running the SELECT * FROM VIEW and getting the error. I can show you what permissions I have. Will that help ?
- mw112009 - Wednesday, January 18, 2017 12:44 PM
It's because you probably don't have permissions to msdb for the job information. You may want to ask someone with more permissions to create a stored procedure the selects from the view with an execute as.
Or enable cross database ownership chaining. Talk to whoever is the DBA for that server.Sue
-
If you try this:
select * from msdb.dbo.sysjobs
and get the same error, it means you don't have permissions to read data from the table. Check your permissions. This isn't something we can do for you.<b - Lynn Pettis - Wednesday, January 18, 2017 3:12 PM
Thank you , This is fixed... Yes it was a permissions issue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply