Monday, February 15, 2016

Reporting services ‘nullreferenceexception’

I spent most of Valentines day this year trying to fix SharePoint 2010 and Reporting services.

I’ve decided to blog about the experience because it was so frustrating and the solution was not what I would have expected.

The sequence of events was’;

  • We removed the ‘ReportCentre’ content database from the SharePoint 2010 farm
  • Moved the data file to another drive in SQL
  • Added the content database back to the farm

The outcome of that was that the SharePoint site collection was online and browseable but none of the SQL Reporting Services Reports would render.

When we looked in the SSRS log files, this is all we got:

webserver!ReportServer_0-1!1e78!02/14/2016-16:21:26:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. ---> System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SharePoint.SPSite.PreinitializeServer(SPRequest request) at Microsoft.SharePoint.SPSite.GetSPRequest() at Microsoft.SharePoint.SPSite.get_Request() at Microsoft.SharePoint.SPSite.get_ReadLocked() at Microsoft.ReportingServices.SharePoint.Objects.RSSPImpSite.get_ReadLocked() at Microsoft.ReportingServices.SharePoint.Server.SharePointServiceHelper.<>c__DisplayClass4.b__3() at Microsoft.ReportingServices.SharePoint.Server.Utility.<>c__DisplayClass5.b__3() at Microsoft.ReportingServices.SharePoint.Objects.RSSPImpSecurity.<>c__DisplayClass1.b__0() at Microsoft.SharePoint.SPSecurity.<>c__DisplayClass4.b__2() at Microsoft.SharePoint.Utilities.SecurityContext.RunAsProcess(CodeToRunElevated secureCode) at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(WaitCallback secureCode, Object param) at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(CodeToRunElevated secureCode) at Microsoft.ReportingServices.SharePoint.Objects.RSSPImpSecurity.StaticRunWithElevatedPrivileges(CodeToRunElevated secureCode) at Microsoft.ReportingServices.SharePoint.Server.Utility.RunElevated(CodeToRunElevated code)

This was of very little help. There were no errors in the ULS logs.

Eventually we realised that if we loaded a report from a different site collection in a different content database, then it worked, so this indicated that the problem was not Reporting Services itself.

We backed up the existing content database thinking that we would try a restore from backup.

During the backup, SharePoint lost the reference to the content database and we then had an ‘Orphan’ content database in the farm. (I still don’t know how or why that happened)

This prevented me from re-attaching the content database because the Id already existed.

So I thought I would restore the site collection to another content database. This failed. Then I discovered I could no longer create a new site collection in the web application because it couldn’t enumerate the content database because of a null reference exception. The orphan database….

Then I found this post from Steve Thomas.

http://mydailytechlog.blogspot.com.au/2011/12/sp2010-cannot-create-new-site.html

This is an extract of his solution:

Solution:

This is a case of at least 1 orphaned database associated to the problematic web application.

1. Run below powershell command to get GUID of web application.

get-spwebapplication | ? {$_.displayname -eq "Problematic Web Application Name"} | fl

2. Run this SQL query against configuration database.

select ID, Name, CAST (properties as xml) from Objects where ID = 'GUID of webapp from Step 1'

3. The results returned from step 2 will have 1 column with the properties as xml. Click on the results and it should open a new window in SQL Mgmt studio with xml output.

4. Search for the word "m_Databases" in the xml output.

5. Carefully read through the tag that stores the above searched word. You should be able to see fld tags. Find out a tag which has fld tag with value null. This is the orphan database. Its GUID is the xml line above where you found null.

6. Cross verify all databases currently associated with the web application and make sure this database is not getting used.

7. Run below powershell script,

$webapp.contentdatabases.delete(‘ID-of-bad-content-db’)

This script will throw an object reference error. Do not worry, it works its magic under the hood.

Try to create a new site collection now and it should work.

Summary: The above problem occurs because if there one entry which has fld value set to null SharePoint thinks that that database is the most suitable one to create new site collections in. But since it is an orphaned database, it is unable to do so and reports the above error in ULS which is repeated every time a new site collection creation request is initiated with through SP user interface or Project Server interface.

 

This allowed me to clear the Orphan content database and then re-attach the database to SharePoint.

Then everything including reporting services started working.

So thanks (not) SSRS for the helpful error message….

And thanks to Steve for having posted a solution that didn’t require ‘hacking’ the config database.