Guidelines to troubleshooting and documenting SQL Server SSRS

A while back I got a question about how to document SQL Server Reporting Services.

I have been very busy, but here is a short post on the subject.

There are a few things to consider when documenting and troubleshooting SSRS.

1. Since 2008, SQL Server reporting services does not depend on IIS anymore.

2. The Reporting Services service is not cluster aware. This means that the databases (ReportServer and ReportServerTempDB) can reside on a clustered instance of SQL Server, but the service cannot be part of a cluster, hence it will not fail over automatically.

3. What would be good to document first is the account settings and permissions. Here is a good script for this:

 

SELECT
 CASE RSCatalog.Type
  WHEN 1 THEN 'Folder'
  ELSE 'Report'
 END AS [Type],
 RSCatalog.Path,
 RSCatalog.Name AS Report,
 Users.UserName,
 Roles.RoleName
-- *
FROM
 [ReportServer].[dbo].[Catalog] RSCatalog
 INNER JOIN [ReportServer].[dbo].[PolicyUserRole] PolicyUserRole
 ON RSCatalog.PolicyID = PolicyUserRole.PolicyID
 INNER JOIN [ReportServer].[dbo].[Roles] Roles
 ON PolicyUserRole.RoleID = Roles.RoleID
 INNER JOIN [ReportServer].[dbo].[Users] Users
 ON PolicyUserRole.UserID = Users.UserID
WHERE
 RSCatalog.Type IN (1,2)
ORDER BY
 RSCatalog.Path,
 RSCatalog.Name,
 Users.UserName

4. Here is how to look at the report execution statistics and performance:

SELECT
 TOP 100
 ExecutionLog.TimeStart,
 ExecutionLog.STATUS,
 RSCatalog.Path,
 RSCatalog.Name AS Report,
 ExecutionLog.UserName,
 ExecutionLog.Format,
 ExecutionLog.Parameters
FROM
 [ReportServer].[dbo].[ExecutionLog] ExecutionLog
 INNER JOIN [ReportServer].[dbo].[Catalog] RSCatalog
 ON ExecutionLog.ReportID = RSCatalog.ItemID
ORDER BY
 ExecutionLog.TimeStart DESC

Here are some scripts for scripting the report definitions, and for searching the report definitions for specific objects.

-- Type
-- 1 Folder
-- 2 Report
-- 3 Resource
-- 4 Linked Report
-- 5 Data Source
-- 6 Model
-- report definition is stored as VARBINARY, so we need to convert it
-- to something readable
SELECT
    ItemID,
    [Path],
    [Name],
    CONVERT(VARCHAR(MAX),
            CONVERT(NVARCHAR(MAX),
            CONVERT(XML, CONVERT(VARBINARY(MAX), Content))))
 AS [ReportDefinition]
 
FROM [ReportServer].[dbo].[Catalog]
WHERE [Type] = 2
-- -------------------------------------------------------------------
-- search for objects in report definitions
-- -------------------------------------------------------------------
 
WITH XMLNAMESPACES
(
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS REP
)
SELECT  c.Path ,
        c.Name ,
        DataSetXML.value('@Name', 'varchar(MAX)') DataSourceName ,
        DataSetXML.value('REP:Query[1]/REP:CommandText[1]', 'varchar(MAX)') CommandText
FROM    ( SELECT    ItemID ,
                    CAST(CAST(Content AS VARBINARY(MAX)) AS XML) ReportXML
          FROM      [ReportServer].[dbo].[Catalog]
          WHERE     Type = 2
        ) ReportXML
        CROSS APPLY ReportXML.nodes('//REP:DataSet') DataSetXML ( DataSetXML )
        INNER JOIN [ReportServer].[dbo].[Catalog] c ON ReportXML.ItemID = c.ItemID
 -- Search by part of the query text
WHERE   ( DataSetXML.value('REP:Query[1]/REP:CommandText[1]', 'varchar(MAX)') ) LIKE '% Enter object name here %'

I hope this helps. There are so many specific details about SSRS, that it is not the task of one post to mention all of them.

 

2 comments to Guidelines to troubleshooting and documenting SQL Server SSRS