Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

SQL Server Reporting Services 2008 R2 Report Server Database Helpful Queries

MSSQLTips author Scott Murray By:   |   Read Comments (15)   |   Related Tips: > 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


Last Update: 8/14/2012


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, December 05, 2012 - 1:48:12 AM - Ashok Read The Tip

Good Article. Thanks a lot..


Sunday, January 06, 2013 - 11:26:34 AM - CK Read The Tip

*** 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


Tuesday, March 12, 2013 - 3:10:10 PM - Gary Read The Tip

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!


Tuesday, March 12, 2013 - 6:51:12 PM - Scott Murray Read The Tip

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.


Wednesday, June 05, 2013 - 8:08:16 AM - Nevarda Read The Tip
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


Wednesday, June 05, 2013 - 8:33:23 AM - scott murray Read The Tip

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 05, 2013 - 10:43:30 AM - Nevarda Read The Tip

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 05, 2013 - 10:51:17 AM - Nevarda Read The Tip

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

 


Monday, June 10, 2013 - 5:03:04 AM - Nevarda Read The Tip

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

 


Monday, June 10, 2013 - 9:35:02 AM - scott murray Read The Tip

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 - 10:16:50 AM - Nevarda Read The Tip

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


Thursday, September 26, 2013 - 12:10:47 PM - Rick Ftich Read The Tip

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?


Thursday, July 31, 2014 - 3:29:03 AM - Yanze Read The Tip

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, July 31, 2014 - 9:24:55 AM - Scott Read The Tip

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.


Tuesday, August 26, 2014 - 7:22:46 PM - Al in SoCal Read The Tip

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!



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.