SQL Reporting Services (SSRS) 2008 R2 Administration Scripts

Below are a few SQL Reporting Services administration scripts that I've created to help me audit usage of the system.

1. Report Usage Count By User For The Previous 30 Days

SELECT  UserName
       ,COUNT(*) AS TimesExecuted
FROM    ReportServer.dbo.ExecutionLog3
WHERE   ItemPath LIKE '%<ReportName>%'
        AND TimeStart > GETDATE() - 30
GROUP BY UserName
ORDER BY 2 DESC

2. Report Usage With Parameters, Row Count, Execution Time, Format and User Name

SELECT  TimeStart
       ,Source
       ,[RowCount]
       ,Parameters
       ,UserName
       ,RequestType
       ,Format
       ,TimeDataRetrieval + TimeProcessing + TimeRendering [TimeInMilliseconds]
FROM    dbo.ExecutionLog3
WHERE   ItemPath LIKE '%<ReportName>%'
        AND TimeStart BETWEEN '3/10/12' AND '6/11/12'
ORDER BY 1 DESC

3. Report Location with Creation Date, Last Execution Date, and Usage Count

SELECT  Name
       ,CreationDate
       ,Path
       ,E.TimesExecuted
       ,E.LastExecutedFROM    Catalog
        INNER JOIN (SELECT  ItemPath
                           ,COUNT(*) AS TimesExecuted
                           ,MAX(TimeStart) AS LastExecuted
                    FROM    ExecutionLog3
                    GROUP BY ItemPath) E
        ON CATALOG.PATH = E.ItemPathWHERE   Path LIKE '/Reports%'
        AND TYPE = 2ORDER BY 2 DESC

Subsystem 'CmdExec' could not be loaded (reason: The specified module could not be found)

After a recent server crash, we had the opprotunity to rebuild the SQL Server instance from the previous nights backups. After going through the various steps to restore the Master and MSDB databases, as well as change the location of the tempdb, resource and model databases, we thought everything was set.

Over the weekend, the server in question was set to backup user databases to a file share using SQLCMD. Upon starting the job, the SQL Agent returned the following error message: "Unable to start execution of step 1 (reason: The CMDEXEC subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended).  The step failed."

After looking throught the SQLAGENT.OUT file (which yielded the same error message), we took a look at the MSDB..SYSSUBSYSTEMS table. Looking in the subsystem_dll column, the issue presented itself.

 SELECT subsystem
               ,subsystem_dll
   FROM msdb..syssubsystems

subsystem  subsystem_dll   
CmdExec    D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLCMDSS90.DLL   

During the server rebuild, the SQL Server program files where installed under the C:\Program Files directory whereas on the crashed server installation, all the old binaries were installed under D:\Program Files. Since we restored the MSDB database from an old backup, those values overwrote the values from the new server install.

In order for the binaries to load, MSDB..SYSSUBSYSTEMS table had to be updated.

UPDATE msdb..syssubsystems
   SET subsystem_dll = REPLACE(subsystem_dll,'D;\','C:\')   
 WHERE subsystem = 'CMDEXEC'

Once the subsystem_dll column has been updated to point to the C:\Program File directory, we restarted the SQL Agent service and checked the SQLAGENT.OUT file again. The error message was gone and the job completed successfully after we restarted it.