Digging into ReportServer Database

I was looking for some information about my ReportServer database and ran across a great script by Scott Hebert that displays a lot of useful information:

http://sqlninja.blogspot.com/2009/01/querying-reportserver-database.html

USE ReportServer

SELECT
         CatalogParent.Name ParentName,
         Catalog.Name ReportName,
         ReportCreatedByUsers.UserName ReportCreatedByUserName,
         Catalog.CreationDate ReportCreationDate,
         ReportModifiedByUsers.UserName ReportModifiedByUserName,
         Catalog.ModifiedDate ReportModifiedDate,
         CountExecution.CountStart TotalExecutions,
         ExecutionLog.InstanceName LastExecutedInstanceName,
         ExecutionLog.UserName LastExecutedUserName,
         ExecutionLog.Format LastExecutedFormat,
         ExecutionLog.TimeStart LastExecutedTimeStart,
         ExecutionLog.TimeEnd LastExecutedTimeEnd,
         ExecutionLog.TimeDataRetrieval LastExecutedTimeDataRetrieval,
         ExecutionLog.TimeProcessing LastExecutedTimeProcessing,
         ExecutionLog.TimeRendering LastExecutedTimeRendering,
         ExecutionLog.Status LastExecutedStatus,
         ExecutionLog.ByteCount LastExecutedByteCount,
         ExecutionLog.[RowCount] LastExecutedRowCount,
         SubscriptionOwner.UserName SubscriptionOwnerUserName,
         SubscriptionModifiedByUsers.UserName SubscriptionModifiedByUserName,
         Subscriptions.ModifiedDate SubscriptionModifiedDate,
         Subscriptions.Description SubscriptionDescription,
         Subscriptions.LastStatus SubscriptionLastStatus,
         Subscriptions.LastRunTime SubscriptionLastRunTime
   FROM
         dbo.Catalog
         JOIN         dbo.Catalog CatalogParent
         ON Catalog.ParentID = CatalogParent.ItemID
         JOIN
         dbo.Users ReportCreatedByUsers
         ON Catalog.CreatedByID = ReportCreatedByUsers.UserID
         JOIN         dbo.Users ReportModifiedByUsers
         ON Catalog.ModifiedByID = ReportModifiedByUsers.UserID
         LEFT JOIN
   (
   SELECT
            ReportID,
            MAX(TimeStart) LastTimeStart
      FROM
            dbo.ExecutionLog
      GROUP BY
            ReportID
   ) LatestExecution
         ON Catalog.ItemID = LatestExecution.ReportID
         LEFT JOIN   (
   SELECT
            ReportID,
            COUNT(TimeStart) CountStart
      FROM
            dbo.ExecutionLog
      GROUP BY
            ReportID
   ) CountExecution
         ON Catalog.ItemID = CountExecution.ReportID
         LEFT JOIN         dbo.ExecutionLog
         ON LatestExecution.ReportID = ExecutionLog.ReportID
      AND LatestExecution.LastTimeStart = ExecutionLog.TimeStart
         LEFT JOIN         dbo.Subscriptions
         ON Catalog.ItemID = Subscriptions.Report_OID
         LEFT JOIN         dbo.Users SubscriptionOwner
         ON Subscriptions.OwnerID = SubscriptionOwner.UserID
         LEFT JOIN         dbo.Users SubscriptionModifiedByUsers
         ON Subscriptions.ModifiedByID = SubscriptionModifiedByUsers.UserID
   ORDER BY
         CatalogParent.Name,
         Catalog.Name

Clean up your SharePoint Features

If you noticed my previous two posts, you'll see that I've run across some feature upgrade issues when moving from SharePoint 2007 to 2010, and then again when applying service pack 1 to SharePoint 2010.

Today I ran across a really nice tool to help remove references to the missing features that may cause issues during your upgrades: http://featureadmin.codeplex.com/

This tool is extremely fast, easy to use, and effective at removing missing/faulty features from SharePoint farms, web applications, site collections, and sites.

SharePoint SP1 aftereffects

If you are running the Enterprise version of SharePoint 2010, you may notice something missing after the upgrade to Service Pack 1.

The Customize Form button may be missing from the List Ribbon's Customize List section.  This button is only available in the Enterprise version of SharePoint and not in Foundation.

To get your button back, navigate to the Top Level Site Settings. Choose Site Collection Features and scroll down to find SharePoint Server Enterprise Site Collection features.  This feature enables InfoPath Forms Services which will enable the Customize Form button.

If you aren't at the Top Level Site Settings and instead find yourself at the Manage Site Features page for your site,  you will see a very similar feature named SharePoint Server Enterprise Site features, but this will not affect the Customize Form button.

SharePoint 2010 SP1 troubleshooting

So our upgrade from 2007 to 2010 a couple of years back wasn't without errors in the log, but most of these referenced outdated, unused features or web templates that had been decommisioned from any production site. They didn't prevent the upgrade from succeeding.

But when I applied SP1 recently, I realized some of these lingering references were again surfacing to give me trouble.  The service pack installed successfully, but the PSConfig wizard failed. Log files contained 3 errors:

• A missing site feature
• A missing web template
• A missing web.config file

The result of this service pack install and failed PSConfig was a sharepoint farm that was completely down. A request to any page generated an error and referenced a correlation ID. Tracking this error down in the log was a looong dead end because it was just referencing services that were down as a result of the failed upgrade. I cursed under my breathe about testing this install on a development system that wasn't identical to production, after all, the development system upgraded to SP1 without a hitch.  Prior to implementing the rollback strategy (yay VM snapshots!) I decided to dive in and see if the upgrade was salvageable.

Our farm consists of an App server and a WFE. Although PSConfig failed on the WFE, the upgrade status in Central Administration said it was fully up to date, so I focused on the App server.

The web template was installed on the WFE, but not the App server. So I copied over the folder from the C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\SiteTemplates folder.

Since the WFE was responsible for Central Admin and all sites, I was surprised to find a site reference in IIS on the App server. Checking the site and folder revealed it was empty, and the site status was stopped.  After ensuring this site was indeed running on the WFE, I deleted the site from IIS on the App server and removed the folder from the wss\Virtual Directories in inetpub.

I performed a powershell search for the missing site feature:
Get-SPFeature | Sort -Property Id

The feature was a FBA Management package developed by Visigo to manage FBA user accounts from the Site Settings page in SharePoint.  This had been deployed to the WFE but not the App server.

It was then time to run PSConfig again. Success. All sites functional again.

Next steps:

1. June 2011 Cumulative Update
2. Office Web Apps with SP1