Missing Server side dependencies - Problems and Solutions

10. August 2011 12:28 by Eric in SharePoint, SQL Server  //   Comments (0)

Quite recently, I have successfully managed the site-collection environment movement from the staging to the live hosting infrastructure with all the corresponding content and permissions. Then, we looked into the "Health Analyzer" from the central administration to find out a few interesting errors, which basically is complaining about "Missing Server side dependencies".

After fiddling around a bit, it turns out that some 3rd-party web-part hasn't been re-installed on the new production environment, which leads to this error to ask me to "Please install any feature/solution which contains this web. One or more web pages are referenced in the database".

Well, it occurs to me that the quickest way to work out the solution is to re-install the 3rd web parts all over again, then turn the features on, then reset the web server, then the problem goes away.

After going through all that, sadly, it didn't work out at all, which got me thinking about what the relationship between the web-parts and pages, because the part of the error message kept saying "WebPart class [xxxxx-xxxxx-xxxxx-xxxxx-xxxxx]" is referenced [11] times on the current farm". Based on that, it must be referenced in the content database somewhere about this information, so that it could keep tracking of all those relationship and load them up into the page load.

After opening the pandora box (Site-collection content database), I came up the following SQL query to narrow down what pages in this particular site collection has been referenced by those 3rd-party web parts.

SELECT distinct Webs.FullUrl, Webs.Title, AllDocs.DirName, AllDocs.LeafName
FROM AllDocs, Sites, AllWebParts, Webs
WHERE Webs.Id = Sites.RootWebId 
AND AllDocs.Id = AllWebParts.tp_PageUrlID
AND Sites.Id = AllDocs.SiteId 
AND tp_WebPartTypeId IN (
    SELECT DISTINCT tp_WebPartTypeId FROM AllWebParts (NOLOCK)

    WHERE tp_WebPartTypeId = 'WebPartClassId')

Once those pages are revealed, all I need to do is to delete site-collection, then remove them from recycle-bin, lastly remove them from site-collection recycle-bin

After a quick iisreset, magically, the issue has gone away.

In summary, this's the procedure of my solution

  • Step 1:  to identify what feature/web-part falling out (Feature-ID and Web-Part Class-ID) 
    Get-SPSolution <SolutionName>
  • if it turns out that  it’s to do with the feature, then disable the feature with ‘-force’ switch, then verify that by 
    stsadm -o displaysolution -name SOLUTION-NAME
  • Step 3: if it’s to do with web-part we have no longer used, then deleted the related pages permanently

    Once we done all that, we could  simply use my script to verify on the SQL Server level:  
    SELECT distinct Webs.FullUrl, Webs.Title, AllDocs.DirName, AllDocs.LeafName
    FROM AllDocs, Sites, AllWebParts, Webs
    WHERE Webs.Id = Sites.RootWebId 
    AND AllDocs.Id = AllWebParts.tp_PageUrlID
    AND Sites.Id = AllDocs.SiteId 
    AND tp_WebPartTypeId IN (
        SELECT DISTINCT tp_WebPartTypeId FROM AllWebParts (NOLOCK)
    
        WHERE tp_WebPartTypeId = 'WebPartClassId')


    If no result-set appear from the SQL query, it means that all related pages have been removed completely from the SharePoint (wait, not 100%, it might still appear from Search simply because Search using different database and caching modules)
  • Step 4: “iisreset -noforce”, then check the “Health Analyzer” for verifications.

 

What an quick-win tip! Hopefully, it will help anyone who has this similar issue later on.

SharePoint User Profile Export from the UserProfile Database

2. August 2011 12:59 by Eric in SharePoint, SQL Server  //   Comments (0)

Just got an interesting requirement from the client I'm consulting with to extract all the entire UserProfile AD user details into a CSV file.

It occurs to me I could probably get them exported from the "Central Administration" - "Manage User Profiles" section, however, as part of the requirement, she also likes to have the manager details the user reports as part of the UP extraction.

Bummer! Plan B obviously requires to write the code solution to export all the user level detail from the site-collection level, then de-duplicate them, put them all together. However, it still doesn't serve the purpose based upon the fact that a lot of UP users might not sign up as a user for all the site-collections.

As a Plan B's back-up plan, I went down to the UserProfile_DB database and UserProfile_Full tables. I'm fully aware of that it's just not the best practice, but under this circumstances, I just couldn't come up with any better approach than that.

Lastly, that's the Query I came up this morning in order to fulfil my task:

SELECT a.NTName, a.Employee, g.FirstName, h.LastName, 
b.Title, e.Department, a.[Office Phone], c.Email, f.Office, a.Manager
,(select top 1 f.Email from UserProfile_Full f 
	where f.NTName = a.Manager
	group by f.NTName, f.Email) as 'Manager Email'
FROM (select
a.NTName,
a.RecordID,
a.Manager,
a.PreferredName as Employee,
b.PropertyVal as [Office Phone]
from UserProfile_Full a, UserProfileValue b
where b.PropertyID=8 and 
a.RecordID=b.RecordID) a -- WorkPhone
left outer join
(select
a.RecordID,
a.PreferredName as Employee,
b.PropertyVal as Title
from
  UserProfile_Full a, UserProfileValue b 
where
b.PropertyID=13 and
a.RecordID=b.RecordID) b -- Title
on a.RecordID=b.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as Email
from
  UserProfile_Full a, UserProfileValue b
where
b.PropertyID=9 and
a.RecordID=b.RecordID) c -- WorkEmail
on a.RecordID=c.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as [Cell Phone]
from
  UserProfile_Full a, UserProfileValue b
where
b.PropertyID=19 and
a.RecordID=b.RecordID) d --CellPhone
on a.RecordID=d.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as Department
from
  UserProfile_Full a, UserProfileValue b
where
b.PropertyID=14 and
a.RecordID=b.RecordID) e --Department
on a.RecordID=e.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as Office
from
  UserProfile_Full a, UserProfileValue b
where
b.PropertyID=11 and
a.RecordID=b.RecordID) f --Office
on a.RecordID=f.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as FirstName
from
  UserProfile_Full a, UserProfileValue b
where
b.PropertyID=4 and
a.RecordID=b.RecordID) g --FirstName
on a.RecordID=g.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as LastName
from
  UserProfile_Full a, UserProfileValue b
where
b.PropertyID=5 and
a.RecordID=b.RecordID) h --LastName
on a.RecordID=h.RecordID
order by a.NTName

As said, it wasn't a pretty solution, but when you are running out of the options, it always can be leveraged as a backup-plan ;)