SQL Server Reporting Services 2008 R2 Report Server Database Helpful Queries

By:   |   Updated: 2012-08-14   |   Comments (29)   |   Related: > Reporting Services Administration


Problem

The ReportServer database stores all the details concerning the SSRS datasources, datasets, and reports and includes details about their parameters, location, run statistics, subscriptions, and queries. Much of the information needed to maintain SSRS can be gleaned from the Report Manager or by connecting to SSRS in Management studio. However, sometimes you need more details than is provided by these methods. What options are available?

Solution

SQL Server Reporting Services (SSRS) installs a ReportServer database which house the nuts and bolts of the Reporting Services Infrastructure. In a previous post, we covered the ExecutionLog table and views included in the ReportServer database which describes the ExecutionLogStorage and related views ExecutionLog, ExecutionLog2, and ExecutionLog3, so we will not cover those areas in depth. However many other tables exist in that database which will be introduced in this article. Although these queries can be extremely helpful for specific needs, PLEASE NOTE that these queries are not supported by Microsoft and future updates and upgrades could break their functionality. Testing an upgrade is, of course, the key when dealing with unsupported queries.

The main tables to be used in the queries include:

  • Catalog: This table contains the main details about SSRS reports and folders. Many of the below queries will center around this table.
  • Datasources: This table contains the root details about how the report datasets connect to its related resource.
  • Subscriptions, Schedule, Notifications, & ActiveSubscriptions: These tables contain information about scheduled report subscriptions.
  • Users: This table contains details about access details for users of the report server including those users running the reports and those users publishing the reports.

Example Queries

Query 1

This provides a basic layer of the reports, folders, and other objects that make up the folder structure of the Report Server.

--QUERY 1
USE ReportServer
GO

SELECT 
CASE WHEN C.Name = '' THEN 'Home' ELSE C.Name END AS ItemName, 
C.Description as Report_Description,
LEN(C.Path) - LEN(REPLACE(C.Path, '/', '')) AS ItemLevel, 
CASE 
WHEN C.type = 1 THEN '1-Folder' 
WHEN C.type = 2 THEN '2-Report' 
WHEN C.type = 3 THEN '3-File' 
WHEN C.type = 4 THEN '4-Linked Report' 
WHEN C.type = 5 THEN '5-Datasource' 
WHEN C.type = 6 THEN '6-Model' 
WHEN C.type = 7 Then '7-ReportPart'
WHEN C.type = 8 Then '8-Shared Dataset'
ELSE '9-Unknown' END AS ItemType, 
CASE WHEN C.Path = '' THEN 'Home' ELSE C.Path END AS Path, 
ISNULL(CASE WHEN CP.Name = '' THEN 'Home' ELSE CP.Name END, 'Home') AS ParentName, 
ISNULL(LEN(CP.Path) - LEN(REPLACE(CP.Path, '/', '')), 0) AS ParentLevel,
ISNULL(CASE WHEN CP.Path = '' THEN ' Home' ELSE CP.Path END, ' Home') AS ParentPath 
FROM 
dbo.Catalog AS CP 
RIGHT OUTER JOIN
dbo.Catalog AS C ON CP.ItemID = C.ParentID

Query 1 Example Results

As noted in the below query results, the item name (folder name, report name, etc), the item type, the item path in the folder structure, and the item's parent are returned in the result set..

Query 1 Results

Query 2

This provides more report level detail about a reports including who created and modified it, when was it last executed and conveys some basic subscription details. Scott Herbent's SQL Ninja blog provided the initial basis for this query, although extensive modification has been made.. 

--Query 2
Complex Catalog Query

USE ReportServer
GO

SELECT
CAT_PARENT.Name AS ParentName,
CAT.Name AS ReportName,
ReportCreatedByUsers.UserName AS ReportCreatedByUserName,
CAT.CreationDate AS ReportCreationDate,
ReportModifiedByUsers.UserName AS ReportModifiedByUserName,
CAT.ModifiedDate AS ReportModifiedDate,
CountExecution.CountStart AS ReportExecuteCount,
EL.InstanceName AS LastExecutedServerName,
EL.UserName AS LastExecutedbyUserName,
EL.TimeStart AS LastExecutedTimeStart,
EL.TimeEnd AS LastExecutedTimeEnd,
EL.Status AS LastExecutedStatus,
EL.ByteCount AS LastExecutedByteCount,
EL.[RowCount] AS LastExecutedRowCount,
SubscriptionOwner.UserName AS SubscriptionOwnerUserName,
SubscriptionModifiedByUsers.UserName AS SubscriptionModifiedByUserName,
SUB.ModifiedDate AS SubscriptionModifiedDate,
SUB.Description AS SubscriptionDescription,
SUB.LastStatus AS SubscriptionLastStatus,
SUB.LastRunTime AS SubscriptionLastRunTime
FROM
dbo.Catalog CAT
INNER JOIN
dbo.Catalog CAT_PARENT
ON 
CAT.ParentID = CAT_PARENT.ItemID
INNER JOIN
dbo.Users ReportCreatedByUsers
ON 
CAT.CreatedByID = ReportCreatedByUsers.UserID
INNER JOIN
dbo.Users ReportModifiedByUsers
ON 
CAT.ModifiedByID = ReportModifiedByUsers.UserID
LEFT OUTER JOIN
(
SELECT
ReportID,
MAX(TimeStart) LastTimeStart
FROM
dbo.ExecutionLog
GROUP BY
ReportID
) AS LatestExecution
ON 
CAT.ItemID = LatestExecution.ReportID
LEFT OUTER JOIN
(
SELECT
ReportID,
COUNT(TimeStart) CountStart
FROM
dbo.ExecutionLog
GROUP BY
ReportID
) AS CountExecution
ON 
CAT.ItemID = CountExecution.ReportID
LEFT OUTER JOIN
dbo.ExecutionLog AS EL
ON 
LatestExecution.ReportID = EL.ReportID
AND 
LatestExecution.LastTimeStart = EL.TimeStart
LEFT OUTER JOIN
dbo.Subscriptions SUB
ON 
CAT.ItemID = SUB.Report_OID
LEFT OUTER JOIN
dbo.Users SubscriptionOwner
ON 
SUB.OwnerID = SubscriptionOwner.UserID
LEFT OUTER JOIN
dbo.Users SubscriptionModifiedByUsers
ON
SUB.ModifiedByID = SubscriptionModifiedByUsers.UserID
ORDER BY
CAT_PARENT.Name,
CAT.Name

Query 2 Example Results

Note in the results below, that a Blank Parent Name is actually the "Home" directory on the report server.

Query 2 Results 1
Query 2 Results 2

Query 3

This provides the scheduling details for our subscription and the related SQLAgent JobID. The SQLAgent JobID can be used to run the "Subscription on an adhoc basis or for 1 time report runs. Additionally, Query 3A is from the SQLServer Central Forums http://www.sqlservercentral.com/Forums/Topic1131922-150-1.aspx#bm1132607 and provides details about the subscription report's parameters, output method and location, and the last run date.

--Query 3 Subscription Query


USE REPORTSERVER

CAT.itemid,
REP_SCH.reportID,
CAT.Name AS 'ReportName',
sub.Report_OID,
REP_SCH.ScheduleID AS 'SQLJobID', 
CASE SCH.recurrencetype 
WHEN 1 THEN 'Once' 
WHEN 3 THEN 
CASE SCH.daysinterval 
WHEN 1 THEN 'Every day' ELSE 'Every other ' + CAST(SCH.daysinterval AS varchar) 
+ ' day.' END WHEN 4 THEN CASE SCH.daysofweek WHEN 1 THEN 'Every ' 
+ CAST(SCH.weeksinterval AS varchar) 
+ ' week on Sunday' WHEN 2 THEN 'Every ' + CAST(SCH.weeksinterval AS varchar) 
+ ' week on Monday' WHEN 4 THEN 'Every ' + CAST(SCH.weeksinterval AS varchar) 
+ ' week on Tuesday' WHEN 8 THEN 'Every ' + CAST(SCH.weeksinterval AS varchar) 
+ ' week on Wednesday' WHEN 16 THEN 'Every ' + CAST(SCH.weeksinterval AS varchar) 
+ ' week on Thursday' WHEN 32 THEN 'Every ' + CAST(SCH.weeksinterval AS varchar) 
+ ' week on Friday' WHEN 64 THEN 'Every ' + CAST(SCH.weeksinterval AS varchar) 
+ ' week on Saturday' WHEN 42 THEN 'Every ' + CAST(SCH.weeksinterval AS varchar) 
+ ' week on Monday, Wednesday, and Friday' WHEN 62 THEN 'Every ' 
+ CAST(SCH.weeksinterval AS varchar) 
+ ' week on Monday, Tuesday, Wednesday, Thursday and Friday' WHEN 126 THEN 'Every ' 
+ CAST(SCH.weeksinterval AS varchar) 
+ ' week from Monday to Saturday' WHEN 127 THEN 'Every ' + CAST(SCH.weeksinterval AS varchar) 
+ ' week on every day' END WHEN 5 THEN CASE SCH.daysofmonth WHEN 1 THEN 'Day ' 
+ '1' + ' of each month' WHEN 2 THEN 'Day ' + '2' + ' of each month'
WHEN 4 THEN 'Day ' + '3' + ' of each month' WHEN 8 THEN 'Day ' + '4' + ' of each month' WHEN 16 THEN 'Day ' + '5' + ' of each month' WHEN 32 THEN 'Day ' + '6' + ' of each month' WHEN 64 THEN 'Day ' + '7' + ' of each month' WHEN 128 THEN 'Day ' + '8' + ' of each month' WHEN 256 THEN 'Day ' + '9'
+ ' of each month' WHEN 512 THEN 'Day ' + '10' + ' of each month' 
WHEN 1024 THEN 'Day ' + '11' + ' of each month' 
WHEN 2048 THEN 'Day ' + '12' + ' of each month' 
WHEN 4096 THEN 'Day ' + '13' + ' of each month' 
WHEN 8192 THEN 'Day ' + '14' + ' of each month' 
WHEN 16384 THEN 'Day ' + '15' + ' of each month' 
WHEN 32768 THEN 'Day ' + '16' + ' of each month' 
WHEN 65536 THEN 'Day ' + '17' + ' of each month' 
WHEN 131072 THEN 'Day ' + '18' + ' of each month' WhEN 262144 THEN 'Day ' + '19' + ' of each month' WHEN 524288 THEN 'Day ' + '20' + ' of each month' WHEN 1048576 THEN 'Day ' + '21' + ' of each month'
WHEN 2097152 THEN 'Day ' + '22' + ' of each month' WHEN 4194304 THEN 'Day ' + '23' + ' of each month' WHEN 8388608 THEN 'Day ' + '24' + ' of each month'
WHEN 16777216 THEN 'Day ' + '25' + ' of each month' WHEN 33554432 THEN 'Day ' + '26' + ' of each month' WHEN 67108864 THEN 'Day ' + '27' + ' of each month'
WHEN 134217728 THEN 'Day ' + '28' + ' of each month' WHEN 268435456 THEN 'Day ' + '29' + ' of each month' WHEN 536870912 THEN 'Day ' + '30' +
' of each month' WHEN 1073741824 THEN 'Day ' + '31' + ' of each month' END WHEN 6 THEN 'The ' + CASE SCH.monthlyweek WHEN 1 THEN 'first' WHEN
2 THEN 'second' WHEN 3 THEN 'third' WHEN 4 THEN 'fourth' WHEN 5 THEN 'last' ELSE 'UNKNOWN' END + ' week of each month on ' + CASE SCH.daysofweek
WHEN 2 THEN 'Monday' WHEN 4 THEN 'Tuesday' ELSE 'Unknown' END ELSE 'Unknown' END + ' at ' + LTRIM(RIGHT(CONVERT(varchar, SCH.StartDate, 100), 7)) 
AS 'ScheduleDetails'
,SCH.RecurrenceType
,CAT.Path AS 'ReportPath'

FROM 
dbo.Catalog AS cat 
INNER JOIN
dbo.ReportSchedule AS REP_SCH
ON CAT.ItemID = REP_SCH.ReportID 
INNER JOIN
dbo.Schedule AS SCH
ON 
REP_SCH.ScheduleID = SCH.ScheduleID 
INNER JOIN
dbo.Subscriptions AS sub 
ON 
sub.SubscriptionID = REP_SCH.SubscriptionID
WHERE 
(LEN(CAT.Name) > 0)
--AND
--CAT.Name like 'Name of Report%' --Can add the Report Name
ORDER BY 'ReportName' 



--OR
--Query 3 Subscription Query
--This code is from: http://www.sqlservercentral.com/Forums/Topic1131922-150-1.aspx#bm1132607 

SELECT
 CAT.[Name] AS RptName
 , U.UserName
 , CAT.[Path]
 , res.ScheduleID AS JobID
 , sub.LastRuntime
 , sub.LastStatus
 , LEFT(CAST(sch.next_run_date AS CHAR(8)) , 4) + '-'
 + SUBSTRING(CAST(sch.next_run_date AS CHAR(8)) , 5 , 2) + '-'
 + RIGHT(CAST(sch.next_run_date AS CHAR(8)) , 2) + ' '
 + CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
 THEN '0' + LEFT(CAST(sch.next_run_time AS VARCHAR(6)) , 1)
 ELSE LEFT(CAST(sch.next_run_time AS VARCHAR(6)) , 2)
 END + ':'
 + CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
 THEN SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)) , 2 , 2)
 ELSE SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)) , 3 , 2)
 END + ':00.000' AS NextRunTime
 , CASE WHEN job.[enabled] = 1 THEN 'Enabled'
 ELSE 'Disabled'
 END AS JobStatus
 , sub.ModifiedDate
 , sub.Description
 , sub.EventType
 , sub.Parameters
 , sub.DeliveryExtension
 , sub.Version
FROM
 dbo.Catalog AS cat
 INNER JOIN dbo.Subscriptions AS sub
 ON CAT.ItemID = sub.Report_OID
 INNER JOIN dbo.ReportSchedule AS res
 ON CAT.ItemID = res.ReportID
 AND sub.SubscriptionID = res.SubscriptionID
 INNER JOIN msdb.dbo.sysjobs AS job
 ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
 INNER JOIN msdb.dbo.sysjobschedules AS sch
 ON job.job_id = sch.job_id
 INNER JOIN dbo.Users U
 ON U.UserID = sub.OwnerID
ORDER BY
 U.UserName
        , RptName

Query 3 Example Results

Query 3 Results 1
Query 3 Results 2

Query 4

The below provides information about who has access to folders and reports and describes the role / level of access the user or group have to that report.

--Query 4 Permissions/Roles

USE ReportServer
GO

SELECT 
CAT.Name
,U.UserName
,ROL.RoleName
,ROL.Description
,U.AuthType
FROM 
dbo.Users U
INNER JOIN 
dbo.PolicyUserRole PUR
ON 
U.UserID = PUR.UserID
INNER JOIN 
dbo.Policies POLICY
ON 
POLICY.PolicyID = PUR.PolicyID
INNER JOIN 
dbo.Roles ROL
ON 
ROL.RoleID = PUR.RoleID
INNER JOIN 
dbo.Catalog CAT
ON 
CAT.PolicyID = POLICY.PolicyID

ORDER BY 
CAT.Name

Query 4 Example Results

Note in the below results that not only is the role name displayed but also the roles description.

Query 4 Results 1

Other Hints

In reviewing the various tables, you will quickly notice that several tables contain XML formated fields. If you need to parse out these details, I would recommend reviewing Sankar Reddy's MSSQLTip: Script to determine SQL Server Reporting Services parameters, path and default values. Make sure you change the XMLNAMESPACE to 2008 in order for the XML to be parsed correctly.

Catalog Table

Let us review some of the tables in the ReportServer database. First, the Configuration table provides specifics on the Report Server setup; however, I would caution you to not make updates directly to this table, and instead use the SSRS Configuration Manager

Config Table

Next, the Users table, displayed below, is the link between the UserID that is assigned by SSRS and the actual UserName (active directory or local) who logs into the Report Server to run or maintain a report. You could use the User Name field to link up to Active Directory, if used, to get additional details concerning a user. Brady Upton's tip, Querying Active Directory Data from SQL Server will get you started querying Active Directory which you could link up to this UserName field.

User Table

Conclusion-Creating an Execution Log SSRS Report

In this tutorial, several of the ReportServer database tables, most importantly Catalog, Subscription, and Users, were reviewed. Several example queries showed some of the powerful information that could be gleamed from these tables, but be cautious about using these tables as they could change in future versions of SQL Server.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-08-14

Comments For This Article




Friday, August 5, 2022 - 4:16:11 PM - John Back To Top (90354)
How do we build a query to see which reports haven't been accessed within 2-3 Months?

Any help will be appreciated!

Monday, May 10, 2021 - 7:03:44 AM - Scott Murray Back To Top (88664)
S. Kumar... You will need to interact with your active directory to check each account.

Monday, May 10, 2021 - 5:00:08 AM - S Kumar Back To Top (88662)
Hello Scott,

With reference to my previous comment....do you know how I can distinguish between username accounts and group accounts that hold the roles and subscriptions...as I only need data for inactive user accounts. Thanks
So main Goal:
1) Extract data for all the reports which have security settings set to accounts by employee names and also the account status (disabled/deactivated/active).

Differentiate Username(Login) Accounts from Group Accounts.
Finding if the User Account is Active or Inactive

Monday, May 10, 2021 - 4:16:44 AM - S Kumar Back To Top (88661)
Hello,

I need to write a query to identify inactive users who have left the company but are still carrying Roles and subscriptions. Also as I only need the inactive user accounts I will have to distinguish between UserName Accounts and Group Accounts. At last I need to create a JOB to monthly extract such user accounts. Can someone help with the query? Thanks!

Tuesday, February 28, 2017 - 11:34:42 PM - Scott Murray Back To Top (46812)

 Sam,

 

I do not work for Microsoft, but my impression it that SSRS is alive and well and as shown with the SQL 2016 upgrade, Microsoft still thinks it is a viable product.

 

Scott

Tuesday, February 28, 2017 - 6:50:38 PM - Sam Back To Top (46798)

 Hello Scott,

What is the future of SSRS moving forward with the introduction of Power BI in BI space? Do you know Microsoft has any near term plan to sunset SSRS?

Should I invest my time learning and doing work in SSRS?

Thank You. Have a nice day.

 

 

Thursday, February 19, 2015 - 10:19:37 PM - Scott Back To Top (36295)

Matthew... I am not aware of another location where these details are saved.

 

Wednesday, February 18, 2015 - 7:32:39 PM - Matthew Nguyen Back To Top (36279)

Hey there!

I had a quick question about joining the report server tables back to msdb.dbo.sysjobs. I noticed that you're rejoining the scheduleid back to 'name' in sysjobs. This is quite problematic for me as we've been renaming so many of our jobs for readability that the join no longer works. I had initially thought that name was simply an attribute, and any joining would reference the jobid in msdb.dbo.sysjobs. 

As a temorary solution, i've discovered that the original 'name' is actually stored in msdb.dbo.sysjobsteps ... and anything with the step name '_step_1' starts off with the original name GUID. From there, i can then join the report server tables to it if I parse it out. Is there any table that can just look at job_id and find out it's original name? 

I hope that made sense. 

Thanks, 
matthew  

Wednesday, October 1, 2014 - 8:09:08 AM - Scott Back To Top (34793)

Sorry... I am not sure what you mean by where the query resides? Those are embedded in the code for SSRS.  I have not seen instances where >12 do not show up in the search.  Sorry.

Wednesday, October 1, 2014 - 5:24:31 AM - Jayakumar Jayakarthikeyan Back To Top (34790)

Scott,

      I got the below query by using the SQL profiler.Also if i enter below 12 letter  in the search for tool bar. the search text doesnt hit the database. if the letter is above 12 letters the query hits the database.

 

Can you please tell me where the below query resides in the sql server 2008 r2 sp2. Awaiting for your reply.

exec sp_executesql N'
SELECT
   C.Type,
   C.PolicyID,
   SD.NtSecDescPrimary,
   C.Name,
   C.Path,
   C.ItemID,
   DATALENGTH( C.Content ) AS [Size],
   C.Description,
   C.CreationDate,
   C.ModifiedDate,
   SUSER_SNAME(CU.Sid),
   CU.UserName,
   SUSER_SNAME(MU.Sid),
   MU.UserName,
   C.MimeType,
   C.ExecutionTime,
   C.Hidden,
   C.SubType,
   C.ComponentID
FROM
   Catalog AS C
   INNER JOIN Users AS CU ON C.CreatedByID = CU.UserID
   INNER JOIN Users AS MU ON C.ModifiedByID = MU.UserID
   LEFT OUTER JOIN SecData AS SD ON C.PolicyID = SD.PolicyID AND SD.AuthType = @AuthType
 WHERE ((C.Name like @Text escape ''*'' or C.Description like @Text escape ''*'')
 And C.Type in (@Type0, @Type1, @Type2, @Type3, @Type4, @Type5, @Type6))',
 N'@AuthType int,@Text nvarchar(16),@Type0 nvarchar(1),@Type1 nvarchar(1),@Type2 nvarchar(1),
 @Type3 nvarchar(1),@Type4 nvarchar(1),@Type5 nvarchar(1),@Type6 nvarchar(1)'
 ,@AuthType=1,@Text=N'%InM0004%',@Type0=N'8',@Type1=N'5',@Type2=N'1',@Type3=N'4',
 @Type4=N'6',@Type5=N'2',@Type6=N'3

 

Thanks!

JAYAKUMAR

Monday, September 29, 2014 - 9:50:03 AM - Jayakumar Back To Top (34751)

Scott,

      Please find my the URL path in the MSDN For more details for the Search For Tool bar.

 

Path: http://social.msdn.microsoft.com/Forums/en-US/6c27e4f9-139c-479b-b0c0-5447cb637ce9/report-manager-timeout-on-searches?forum=sqlreportingservices

 

Thanks!

Jayakumar.J

Monday, September 29, 2014 - 9:47:05 AM - Jayakumar Back To Top (34750)

Scott,

I have an issue with the Search For tool bar in SSRS consider the below scenario.

01000 Reporting Change Configuration consider this as an report name. if we give the full character against  the search for tool bar, it works or atleast01000 Report (12) characters the search for button works.

 

Can you please sort it out if there is any setting that we can change that is limiting the search to twelve characters?

 

Thanks!

Jayakumar.J

Thursday, August 28, 2014 - 11:12:58 AM - Al in SoCal Back To Top (34319)

Thanks for that - we've since tested it and it seems to work w/o issue.  Didn't think about the RDL file however.  Thanks for the info!

Thursday, August 28, 2014 - 8:57:11 AM - Scott Murray Back To Top (34309)

yes the catalog description can be updated although to be honest, I would test it first as I have never completed an update that way. Also note that the description in the project rdl file will reflect the old description, not the one on the report server.

Tuesday, August 26, 2014 - 7:22:46 PM - Al in SoCal Back To Top (34277)

Great article AND comments!

 

Question: If I have a large inventory of reports and want to retroactively go back and update them all with descriptions - can I do so using the catalog's description field?  If not - is there a way to programatically update the description field (not subscription description but the report description)

 

Thanks!

Thursday, July 31, 2014 - 9:24:55 AM - Scott Back To Top (33957)

Yanze,

You might want to see if the user run the browser in "run as admin" mode if they are on Win 7 or Win 8.  Would need to know what error they are getting in IE to trouble shoot specifically.

Thursday, July 31, 2014 - 3:29:03 AM - Yanze Back To Top (33952)

Hi Sir,

I just want to ask if you've ever encountered SSRS user login problem? This happens to some of our users, whenever they try to access the URL of our SSRS Reports, the login just keeps appearing although they've already provided the correct user name and password(this is using a Mozilla Firefox browser). And when they use IE browser, different error appears. This puzzled us because other users can access the URL without having any troubles. Is there other security features in SSRS that might hinder these users to go in through the SSRS Reports?

Hoping for your kind help.

Thanks...

Thursday, September 26, 2013 - 12:10:47 PM - Rick Ftich Back To Top (26960)

This is great stuff but now we are bringing up SSRS reporting in SharePoint full integration mode.

I haven't seen any articles about SSRS execution queries and Subcription management queries in the SharePoint environment (I don't even know yet where the reporting tables are...)

Do you know where I can find such articles?

Monday, June 10, 2013 - 10:16:50 AM - Nevarda Back To Top (25376)

Hi Scott,

yes i aggree with you, thanks.

i will however have to go through each one to find the ones in particular that are reliant on this.

I have approx 1400 reports in total to sift through that use shared schedules in order to do that but only about 180 that are

relevant to this particular problem.

Thanks again,

Nevarda

Monday, June 10, 2013 - 9:35:02 AM - scott murray Back To Top (25371)

One option is to put them on different schedules, but the actual schedules (times, day of the week) etc are same. 

 

Monday, June 10, 2013 - 5:03:04 AM - Nevarda Back To Top (25364)

problem found - subscriptions are using a shared schedule :(

I have no way of executing a single report if it uses the shared schedule.

thanks for the advice... not sure what ill do now

 

Wednesday, June 5, 2013 - 10:51:17 AM - Nevarda Back To Top (25309)

Unless ive joined these wrong and thats causing the id to show as being the same?

 

Wednesday, June 5, 2013 - 10:43:30 AM - Nevarda Back To Top (25308)

Hi Scott,

Thanks for the reply. Im not sure if i understand you correctly.

I have one report that has 5 separate subscriptions. each subscription has different parameter choices and gets emailed to different addresses.

I have written script and looked at examples where i can show any failed subscriptions however the schedule_id is the same for all 5 subscriptions. when i run the following script to determine the schedule id of a report that didnt run so that i can execute it again using sql script as shown below in green, then it runs all 5 subscriptions again because the id is the same where i only want to run the one subscription of the 5 that failed.

 Use ReportServer

GO

SELECT     Schedule.ScheduleID AS SQLAgent_Job_Name, Subscriptions.Description AS sub_desc, Subscriptions.DeliveryExtension AS sub_delExt, [Catalog].Name AS ReportName, [Catalog].Path AS ReportPath,Subscriptions.LastStatus  FROM ReportSchedule

INNER JOIN Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID

INNER JOIN Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID INNER JOIN [Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID

WHERE Catalog.Name like '%Example_Report_Name%'

This returns a schedule ID of the report in question which i insert through a variable into the script below which executes it again however it executes all 5 subscriptions

 

USE msdb ;

GO

EXEC dbo.sp_start_job N'D91E2A33-5B31-40AA-B100-9FC6D828891E' ;

GO 

Wednesday, June 5, 2013 - 8:33:23 AM - scott murray Back To Top (25305)

If all 5 reports are under the same ID then you cannot split them up.  It make be better if you create 5 subscriptions.

Wednesday, June 5, 2013 - 8:08:16 AM - Nevarda Back To Top (25304)
Read The Tip

HI, I'm not sure if any of you covered this but please could you assist?

I have, for example, a report that has many parameters that change the reports result. Because i need to have more than five(5) results from the same report, i have made 5 subscriptions on it choosing different parameters for each run. My issue is every now and then, one of the five will fail due to some error in calculation because data changes etc. I want to know if there is a way to re-run only that specific subscription for that report of the five that are setup and not the one subscription id that will run all 5 again.

does that make sense?

I cant just run the entire subscription again because it emails many institutions that rely on certain information from the reports being imported into their databases from the email recieved and a duplicate email is totally unacceptable

Thanks in advance,

Nevarda

Tuesday, March 12, 2013 - 6:51:12 PM - Scott Murray Back To Top (22764)

Gary..... Since you are sharing the same DB, you will only have only one Catalog table, so once you deploy, you are actually deploying, in essence, both servers as their backend is to the same database.  You can use the ExecutionLogStorage to assist with what is running on each, but if you only have one reportserver db, you will have only one catalog table to deploy a report to.

Tuesday, March 12, 2013 - 3:10:10 PM - Gary Back To Top (22757)

We have a scale-out deployment, with two report servers sharing one reportserver database on a third db server.  The two servers are used for Development and Production, rather than for load sharing.  So in this case, is it possible to determine from the Catalog table, which report server the record points to?

Let's say we deploy a new report to the Dev report server.  The customer reviews & approves the report, and we deploy the same report (identical RDL) to the identical folder path on the Production report server.  Does this result in a second record added to the Catalog table?  If so, where in the Catalog record can we determine which report server is pointing to each record.  Or so long as everything is identical, do the two servers share one Catalog record? 

Or let's say the same report name is deployed to the same path, but a very minor change is made to the RDL, so now the reports are not identical.  In this case obviously there would be two Catalog records, but again how would we tell which record goes with which report server?  (since report names and paths are identical).

Thanks!

Sunday, January 6, 2013 - 11:26:34 AM - CK Back To Top (21278)

*** I have formattd the codes above. Hopefully they save your time if interested in the codes***

 

--Query 1: This provides a basic layer of the reports, folders, and other objects that make up the folder structure of the Report Server.

USE reportserver

go

SELECT CASE
         WHEN C.name = '' THEN 'Home'
         ELSE C.name
       END                                                      AS ItemName,
       C.description                                            AS
       Report_Description,
       Len(C.path) - Len(Replace(C.path, '/', ''))              AS ItemLevel,
       CASE
         WHEN C.type = 1 THEN '1-Folder'
         WHEN C.type = 2 THEN '2-Report'
         WHEN C.type = 3 THEN '3-File'
         WHEN C.type = 4 THEN '4-Linked Report'
         WHEN C.type = 5 THEN '5-Datasource'
         WHEN C.type = 6 THEN '6-Model'
         WHEN C.type = 7 THEN '7-ReportPart'
         WHEN C.type = 8 THEN '8-Shared Dataset'
         ELSE '9-Unknown'
       END                                                      AS ItemType,
       CASE
         WHEN C.path = '' THEN 'Home'
         ELSE C.path
       END                                                      AS Path,
       Isnull(CASE
                WHEN CP.name = '' THEN 'Home'
                ELSE CP.name
              END, 'Home')                                      AS ParentName,
       Isnull(Len(CP.path) - Len(Replace(CP.path, '/', '')), 0) AS ParentLevel,
       Isnull(CASE
                WHEN CP.path = '' THEN ' Home'
                ELSE CP.path
              END, ' Home')                                     AS ParentPath
FROM   dbo.catalog AS CP
       RIGHT OUTER JOIN dbo.catalog AS C
                     ON CP.itemid = C.parentid
                    
                     USE reportserver

go

/* Query 2 - This provides more report level detail about a reports including who created and modified it, when was it last executed and conveys some basic subscription details. Scott Herbent's SQL Ninja blog provided the initial basis for this query, although extensive modification has been made.*/


SELECT CAT_PARENT.name                      AS ParentName,
       CAT.name                             AS ReportName,
       ReportCreatedByUsers.username        AS ReportCreatedByUserName,
       CAT.creationdate                     AS ReportCreationDate,
       ReportModifiedByUsers.username       AS ReportModifiedByUserName,
       CAT.modifieddate                     AS ReportModifiedDate,
       CountExecution.countstart            AS ReportExecuteCount,
       EL.instancename                      AS LastExecutedServerName,
       EL.username                          AS LastExecutedbyUserName,
       EL.timestart                         AS LastExecutedTimeStart,
       EL.timeend                           AS LastExecutedTimeEnd,
       EL.status                            AS LastExecutedStatus,
       EL.bytecount                         AS LastExecutedByteCount,
       EL.[rowcount]                        AS LastExecutedRowCount,
       SubscriptionOwner.username           AS SubscriptionOwnerUserName,
       SubscriptionModifiedByUsers.username AS SubscriptionModifiedByUserName,
       SUB.modifieddate                     AS SubscriptionModifiedDate,
       SUB.description                      AS SubscriptionDescription,
       SUB.laststatus                       AS SubscriptionLastStatus,
       SUB.lastruntime                      AS SubscriptionLastRunTime
FROM   dbo.catalog CAT
       INNER JOIN dbo.catalog CAT_PARENT
               ON CAT.parentid = CAT_PARENT.itemid
       INNER JOIN dbo.users ReportCreatedByUsers
               ON CAT.createdbyid = ReportCreatedByUsers.userid
       INNER JOIN dbo.users ReportModifiedByUsers
               ON CAT.modifiedbyid = ReportModifiedByUsers.userid
       LEFT OUTER JOIN(SELECT reportid,
                              Max(timestart) LastTimeStart
                       FROM   dbo.executionlog
                       GROUP  BY reportid) AS LatestExecution
                    ON CAT.itemid = LatestExecution.reportid
       LEFT OUTER JOIN(SELECT reportid,
                              Count(timestart) CountStart
                       FROM   dbo.executionlog
                       GROUP  BY reportid) AS CountExecution
                    ON CAT.itemid = CountExecution.reportid
       LEFT OUTER JOIN dbo.executionlog AS EL
                    ON LatestExecution.reportid = EL.reportid
                       AND LatestExecution.lasttimestart = EL.timestart
       LEFT OUTER JOIN dbo.subscriptions SUB
                    ON CAT.itemid = SUB.report_oid
       LEFT OUTER JOIN dbo.users SubscriptionOwner
                    ON SUB.ownerid = SubscriptionOwner.userid
       LEFT OUTER JOIN dbo.users SubscriptionModifiedByUsers
                    ON SUB.modifiedbyid = SubscriptionModifiedByUsers.userid
ORDER  BY CAT_PARENT.name, CAT.name

--Query 3 Subscription Query


USE reportserver

SELECT CAT.itemid,
       REP_SCH.reportid,
       CAT.name                                                AS 'ReportName',
       sub.report_oid,
       REP_SCH.scheduleid                                      AS 'SQLJobID',
       CASE SCH.recurrencetype WHEN 1 THEN 'Once' WHEN 3 THEN CASE
       SCH.daysinterval
       WHEN 1 THEN 'Every day' ELSE 'Every other ' + Cast(SCH.daysinterval AS
       VARCHAR)
       + ' day.' END WHEN 4 THEN CASE SCH.daysofweek WHEN 1 THEN 'Every ' +
       Cast(SCH.weeksinterval AS VARCHAR) + ' week on Sunday' WHEN 2 THEN
       'Every ' +
       Cast(SCH.weeksinterval AS VARCHAR) + ' week on Monday' WHEN 4 THEN
       'Every ' +
       Cast(SCH.weeksinterval AS VARCHAR) + ' week on Tuesday' WHEN 8 THEN
       'Every ' +
       Cast(SCH.weeksinterval AS VARCHAR) + ' week on Wednesday' WHEN 16 THEN
       'Every '
       +
       Cast(SCH.weeksinterval AS VARCHAR) + ' week on Thursday' WHEN 32 THEN
       'Every ' +
       Cast(SCH.weeksinterval AS VARCHAR) + ' week on Friday' WHEN 64 THEN
       'Every ' +
       Cast(SCH.weeksinterval AS VARCHAR) + ' week on Saturday' WHEN 42 THEN
       'Every ' +
       Cast(SCH.weeksinterval AS VARCHAR) +
       ' week on Monday, Wednesday, and Friday'
       WHEN 62 THEN 'Every '
       + Cast(SCH.weeksinterval AS VARCHAR) +
       ' week on Monday, Tuesday, Wednesday, Thursday and Friday' WHEN 126 THEN
       'Every ' + Cast(SCH.weeksinterval AS VARCHAR) +
       ' week from Monday to Saturday'
       WHEN 127 THEN 'Every ' + Cast(SCH.weeksinterval AS VARCHAR) +
       ' week on every day' END WHEN 5 THEN CASE SCH.daysofmonth WHEN 1 THEN
       'Day ' +
       '1' + ' of each month' WHEN 2 THEN 'Day ' + '2' + ' of each month'WHEN 4
       THEN
       'Day ' + '3' + ' of each month' WHEN 8 THEN 'Day ' + '4' +
       ' of each month' WHEN
       16 THEN 'Day ' + '5' + ' of each month' WHEN 32 THEN 'Day ' + '6' +
       ' of each month' WHEN 64 THEN 'Day ' + '7' + ' of each month' WHEN 128
       THEN
       'Day ' + '8' + ' of each month' WHEN 256 THEN 'Day ' + '9'+
       ' of each month'
       WHEN 512 THEN 'Day ' + '10' + ' of each month' WHEN 1024 THEN 'Day ' +
       '11' +
       ' of each month' WHEN 2048 THEN 'Day ' + '12' + ' of each month' WHEN
       4096 THEN
       'Day ' + '13' + ' of each month' WHEN 8192 THEN 'Day ' + '14' +
       ' of each month'
       WHEN 16384 THEN 'Day ' + '15' + ' of each month' WHEN 32768 THEN 'Day ' +
       '16' +
       ' of each month' WHEN 65536 THEN 'Day ' + '17' + ' of each month' WHEN
       131072
       THEN 'Day ' + '18' + ' of each month' WHEN 262144 THEN 'Day ' + '19' +
       ' of each month' WHEN 524288 THEN 'Day ' + '20' + ' of each month' WHEN
       1048576
       THEN 'Day ' + '21' + ' of each month'WHEN 2097152 THEN 'Day ' + '22' +
       ' of each month' WHEN 4194304 THEN 'Day ' + '23' + ' of each month' WHEN
       8388608
       THEN 'Day ' + '24' + ' of each month'WHEN 16777216 THEN 'Day ' + '25' +
       ' of each month' WHEN 33554432 THEN 'Day ' + '26' + ' of each month' WHEN
       67108864 THEN 'Day ' + '27' + ' of each month'WHEN 134217728 THEN 'Day '
       + '28'
       + ' of each month' WHEN 268435456 THEN 'Day ' + '29' + ' of each month'
       WHEN
       536870912 THEN 'Day ' + '30' +' of each month' WHEN 1073741824 THEN
       'Day ' +
       '31' + ' of each month' END WHEN 6 THEN 'The ' + CASE SCH.monthlyweek
       WHEN 1
       THEN 'first' WHEN 2 THEN 'second' WHEN 3 THEN 'third' WHEN 4 THEN
       'fourth' WHEN
       5 THEN 'last' ELSE 'UNKNOWN' END + ' week of each month on ' + CASE
       SCH.daysofweek WHEN 2 THEN 'Monday' WHEN 4 THEN 'Tuesday' ELSE 'Unknown'
       END
       ELSE 'Unknown'
       END + ' at '
       + Ltrim(RIGHT(CONVERT(VARCHAR, SCH.startdate, 100), 7)) AS
       'ScheduleDetails',
       SCH.recurrencetype,
       CAT.path                                                AS 'ReportPath'
FROM   dbo.catalog AS cat
       INNER JOIN dbo.reportschedule AS REP_SCH
               ON CAT.itemid = REP_SCH.reportid
       INNER JOIN dbo.schedule AS SCH
               ON REP_SCH.scheduleid = SCH.scheduleid
       INNER JOIN dbo.subscriptions AS sub
               ON sub.subscriptionid = REP_SCH.subscriptionid
WHERE  ( Len(CAT.name) > 0 )
--AND

--CAT.Name like 'Name of Report%' --Can add the Report Name

ORDER  BY 'ReportName'

--OR--Query 3 Subscription Query--This code is from: http://www.sqlservercentral.com/Forums/Topic1131922-150-1.aspx#bm1132607 


SELECT CAT.[name]                                                   AS RptName,
       U.username,
       CAT.[path],
       res.scheduleid                                               AS JobID,
       sub.lastruntime,
       sub.laststatus,
       LEFT(Cast(sch.next_run_date AS CHAR(8)), 4)
       + '-'
       + Substring(Cast(sch.next_run_date AS CHAR(8)), 5, 2)
       + '-'
       + RIGHT(Cast(sch.next_run_date AS CHAR(8)), 2)
       + ' ' + CASE WHEN Len(Cast(sch.next_run_time AS VARCHAR(6))) = 5 THEN '0'
       + LEFT
       (Cast(sch.next_run_time AS VARCHAR(6)), 1) ELSE
       LEFT(Cast(sch.next_run_time AS
       VARCHAR(6)), 2) END + ':' + CASE WHEN Len(Cast(sch.next_run_time AS
       VARCHAR(6)))
       = 5 THEN Substring(Cast(sch.next_run_time AS VARCHAR(6)), 2, 2) ELSE
       Substring(
       Cast(sch.next_run_time AS VARCHAR(6)), 3, 2) END + ':00.000' AS
       NextRunTime,
       CASE
         WHEN job.[enabled] = 1 THEN 'Enabled'
         ELSE 'Disabled'
       END                                                          AS JobStatus
       ,
       sub.modifieddate,
       sub.description,
       sub.eventtype,
       sub.parameters,
       sub.deliveryextension,
       sub.version
FROM   dbo.catalog AS cat
       INNER JOIN dbo.subscriptions AS sub
               ON CAT.itemid = sub.report_oid
       INNER JOIN dbo.reportschedule AS res
               ON CAT.itemid = res.reportid
                  AND sub.subscriptionid = res.subscriptionid
       INNER JOIN msdb.dbo.sysjobs AS job
               ON Cast(res.scheduleid AS VARCHAR(36)) = job.[name]
       INNER JOIN msdb.dbo.sysjobschedules AS sch
               ON job.job_id = sch.job_id
       INNER JOIN dbo.users U
               ON U.userid = sub.ownerid
ORDER  BY U.username,
          rptname
         
--Query 4 Permissions/Roles
USE reportserver

go

SELECT CAT.name,
       U.username,
       ROL.rolename,
       ROL.description,
       U.authtype
FROM   dbo.users U
       INNER JOIN dbo.policyuserrole PUR
               ON U.userid = PUR.userid
       INNER JOIN dbo.policies POLICY
               ON POLICY.policyid = PUR.policyid
       INNER JOIN dbo.roles ROL
               ON ROL.roleid = PUR.roleid
       INNER JOIN dbo.catalog CAT
               ON CAT.policyid = POLICY.policyid
ORDER  BY CAT.name

Wednesday, December 5, 2012 - 1:48:12 AM - Ashok Back To Top (20750)

Good Article. Thanks a lot..














get free sql tips
agree to terms