Friday, 12 August 2011

Diagnose MissingWebPart and MissingAssembly issues from the SharePoint Health Analyzer using PowerShell

After posting articles on diagnosing MissingFeature and MissingSetupFile errors in the SharePoint 2010 Health Analyzer and Test-SPContentDatabase operation, I have become slightly obsessed with doing the same for other errors, too. I have also had my fair share of them on migration projects recently, so I can’t be the only one experiencing them at the moment.

In this article I am going to focus on MissingWebPart and MissingAssembly errors. As stated in previous articles, there is no silver bullet to solving these errors in all cases, but the scripts offered here will allow you troubleshoot the errors further to find exactly where they are happening in the content database. Once you know this, you have a fighting chance of being able to solve the problem.

MissingWebPart Error

In this example, I have received the following error whilst running a Test-SPContentDatabase operation after a content database migration from SharePoint 2007 to 2010. It also appears in the SharePoint Health Analyzer under the “Configuration” category with the title “Missing server side dependencies”:

Category        : MissingWebPart
Error           : True
UpgradeBlocking : False
Message         : WebPart class [4575ceaf-0d5e-4174-a3a1-1a623faa919a] is referenced [2] times in the database [SP2010_Content], but is not installed on the current farm. Please install any feature/solution which contains this web part.
Remedy          : One or more web parts are referenced in the database [SP2010_Content], but are not installed on the current farm. Please install any feature or solution which contains these web  parts.

As you can see, the error gives you a “WebPart class” GUID, the name of the content database, and how many times it is referenced in the database, but little else. What we need to find out here is either the name of the web part or on which pages it is referenced in the database.

For this I am going to reuse the Run-SQLQuery PowerShell script that I introduced in my article on MissingSetupFile errors:

function Run-SQLQuery ($SqlServer, $SqlDatabase, $SqlQuery)
{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server =" + $SqlServer + "; Database =" + $SqlDatabase + "; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
}

Once you have loaded the function in a PowerShell console, you can run it by using the Run-SQLQuery command with the options relevant to your deployment. For [MissingWebPart] errors, you need to run a SQL SELECT query on the “AllDocs” table in the content database exhibiting the problem, joining to the “AllWebParts” table in order to find details about the missing web part. For example, you would type the following command to find details of the web part with the class ID “4575ceaf-0d5e-4174-a3a1-1a623faa919a”, as reported in the error above:

Run-SQLQuery -SqlServer "SQLSERVER" -SqlDatabase "SP2010_Content" -SqlQuery "SELECT * from AllDocs inner join AllWebParts on AllDocs.Id = AllWebParts.tp_PageUrlID where AllWebParts.tp_WebPartTypeID = '4575ceaf-0d5e-4174-a3a1-1a623faa919a'" | select Id, SiteId, DirName, LeafName, WebId, ListId, tp_ZoneID, tp_DisplayName | Format-List

Yes, it is a pretty long command, but it will produce a very useful output, as shown in this example:

Id             : 6ab5e70b-60d8-4ddf-93cb-6a93fbc410be
SiteId         : 337c5721-5050-46ce-b112-083ac52f7f26
DirName        : News/Pages
LeafName       : ArticleList.aspx
WebId          : dcc93f3e-437a-4fae-acea-bb15d5c4ea7d
ListId         : 7e13fe6c-3670-4d46-9601-832e3eb6a1e4
tp_ZoneID      : Body
tp_DisplayName :

Id             : b3fcfcd2-2f02-4fe9-93e4-9c9b5ecddf5b
SiteId         : 337c5721-5050-46ce-b112-083ac52f7f26
DirName        : Pages
LeafName       : Welcome.aspx
WebId          : 2ae0de59-a008-4244-aa66-d8f76c79f1ad
ListId         : d8f083f0-16b9-43d0-9aaf-4e9fffecd6cc
tp_ZoneID      : RightColumnZone
tp_DisplayName :

This tells us that the web part has been found on two pages (the references mentioned in the MissingWebPart error). SiteId tells us the site collection and WebId the site where the pages are located. We also have a DirName showing the relative path and the page name itself against the LeafName property. If you’re lucky, you might get the display name of the web part against the tp_DisplayName property, but if not, you should at least be able to tell which zone the web part has been added to by looking at the tp_ZoneID property.

Easily the best way of resolving these issues is to do as the error suggests and install the missing feature or solution containing the web part, but if this is not possible or feasible to do in your scenario, we can discover the site collection URL from the GUIDs using PowerShell and then remove the offending web parts from the pages specified.

To find the site collection URL using the information output from the query, type the following command:

$site = Get-SPSite -Limit all | where {$_.Id -eq "337c5721-5050-46ce-b112-083ac52f7f26"}
$site.Url

One you have the site collection URL, you can use the relative path specified by the DirName property to find the location of the file. To remove the web part from the page, type the page URL in the browser and add ?contents=1 to the end of it. For example, to open the web part maintenance page for the ArticleList.aspx page specified in the output, type the following URL in the browser:

http://portal/news/pages/articlelist.aspx?contents=1

You can then highlight the offending web part (normally called ErrorWebPart for MissingWebPart errors) by ticking the box and clicking Delete. The screenshot below shows a web part maintenance page to give you an idea of the UI, but not an example of an ErrorWebPart as I had already removed them!

image

Note: If you remove an ErrorWebPart from a publishing page with versioning switched on, you may have to delete all earlier versions of the page before the error disappears from the SharePoint Health Analyzer or Test-SPContentDatabase report. This is because the web part will still be referenced from these versions, even though you removed it from the currently published page.

MissingAssembly Error

MissingAssembly errors look similar to this one:

Category        : MissingAssembly
Error           : True
UpgradeBlocking : False
Message         : Assembly [PAC.SharePoint.Tagging, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b504d4b6c1e1a6e5] is referenced in the database [SP2010_Content], but is not installed on the current farm. Please install any feature/solution which contains this assembly.
Remedy          : One or more assemblies are referenced in the database [SP2010_Content], but are not installed on the current farm. Please install any feature or solution which contains these assemblies.

I normally find MissingAssembly errors appear as the result of an event receiver, which is still registered on a list or library but part of a feature/solution no longer present on the farm.

In most cases, you may be able to look at the assembly name reported in this error and know what the problem is straight away. As before, the best way of resolving this is to reinstall the missing solution file. However, if you are not able to install the solution (e.g., maybe it only works in SharePoint 2007 and not 2010), then you may want to find the lists where the event receiver is installed and either remove the event receiver from the lists or delete the lists themselves.

To troubleshoot this issue we can re-use the Run-SQLQuery function used to help find missing web parts above. The table we need to look at this time though is called “EventReceivers”. For example, you would type the following command to find details of the assembly called “PAC.SharePoint.Tagging, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b504d4b6c1e1a6e5”, as reported in the error above:

Run-SQLQuery -SqlServer "SQLSERVER" -SqlDatabase "SP2010_Content" -SqlQuery "SELECT * from EventReceivers where Assembly = ‘PAC.SharePoint.Tagging, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b504d4b6c1e1a6e5'" | select Id, Name, SiteId, WebId, HostId, HostType | Format-List

This will produce an output similar to the following:

Id       : 657a472f-e51d-428c-ab98-502358d87612
Name     :
SiteId   : 337c5721-5050-46ce-b112-083ac52f7f26
WebId    : 2ae0de59-a008-4244-aa66-d8f76c79f1ad
HostId   : 09308020-45a8-41e4-bbc0-7c8d8cd54132
HostType : 2

Id       : 0f660612-6be0-401e-aa1d-0ede7a9af8da
Name     :
SiteId   : 337c5721-5050-46ce-b112-083ac52f7f26
WebId    : 2ae0de59-a008-4244-aa66-d8f76c79f1ad
HostId   : 09308020-45a8-41e4-bbc0-7c8d8cd54132
HostType : 2

As with the MissingWebPart error before, we can use these GUIDs to get the site collection and site hosting the list with the missing event receiver, as follows:

$site = Get-SPSite -Limit all | where {$_.Id -eq "337c5721-5050-46ce-b112-083ac52f7f26"}
$web = $site | Get-SPWeb -Limit all | where {$_.Id -eq "2ae0de59-a008-4244-aa66-d8f76c79f1ad"}
$web.Url

The HostId property is the GUID of the object containing the event receiver. The HostType is the object type – in this case, HostType “2” means the event receiver host is a list. You can look at the other host types by checking this article on MSDN: http://msdn.microsoft.com/en-us/library/ee394866(v=prot.13).aspx.

Now we know the GUID refers to a list, we can get it using PowerShell with this command:

$list = $web.Lists | where {$_.Id -eq "09308020-45a8-41e4-bbc0-7c8d8cd54132"}

To remove the list completely, type the following command:

$list.Delete()

To keep the list intact and just remove the offending event receiver, copy the Id property from the Run-SQLQuery output into this command:

$er = $list.EventReceivers | where {$_.Id -eq "657a472f-e51d-428c-ab98-502358d87612"}
$er.Delete()

If you do decide to delete the list completely, ensure you also remove it from the site Recycle Bin and Site Collection Recycle Bin to ensure the file is removed from the content database. If not, the error may not disappear from the Health Analyzer or Test-SPContentDatabase operation.

If you found this article looking for information on how to diagnose MissingFeature issues in the SharePoint Health Analyzer, rather than MissingWebPart or MissingAssembly issues, then have a look at this article for help. For information on troubleshooting MissingSetupFile errors, take a look at this article.

32 comments:

  1. I tried this script for missing assembly and I get blank results. But I have the missiongAssembly error in my error log. How do i go about that?
    I am running this script on the db that is already mounted on the 2010 application.

    THanks

    ReplyDelete
  2. It may be that the MissingAssembly error does not refer to an event receiver but something else. Do you recognise the assembly name? What does it contain? Can it be reinstalled on the 2010 farm?

    ReplyDelete
  3. Your solution with MissingFeature error helped me generate site language varations without Errors. When I tried to create site varations SharePoint would report that it cannot create those sites cause they contain definitions of features that no longer exist in my farm. It worked like a charm :) The solution above with MissingAssemblies will also help me cause I developed some eventHandlers with VS2010 and after retracting them and runnin farm update to ServicePack1 I started getting those errors too. Great work man!

    ReplyDelete
  4. Excellent stuff. Quite interesting info on the errors you were getting with variations - Hadn't considered that scenario.

    ReplyDelete
  5. Is there anything similar that we could run on the SP 2007 farm?

    ReplyDelete
  6. Very helpful information. Good job! RC

    ReplyDelete
  7. Thanks a lot Phil, this is really a great article !!!
    It helps me a lot in a SharePoint 2010 complex Migration context.

    Actualy for MissingFeature there is a really great tool available here : http://featureadmin.codeplex.com.
    It scan the whole farm and give you the corrupted feature references and can remove them directly.

    Regarding the MissingAssembly/SetupFile/WebPart, I have created an automated script using your examples to get information on all generated error from the sharepoint Health Analyser.

    Again, many thanks !

    ReplyDelete
  8. i am having trouble getting the missing assemblies command to show me the list. I have the guid but when i run the $list = it just goes back to the prompt.

    Any ideas

    ReplyDelete
  9. When I run the .\findmissingassemby.ps1 script i notice that I get an empty
    Id:
    Name:
    SiteID:
    WebID:
    HostID:
    HostType:

    followed by others that actually contain data. I have removed the list I could find but this still pops up. I maybe looking at this wrong but I'm not sure how I can remove something that returns empty (null) or whatever it may be. Any ideas?

    ReplyDelete
  10. @swtjen01 - I have seen this also and it can be ignored. Check with the Health Analyzer to prove all missing server side dependency errors have been resolved.

    ReplyDelete
    Replies
    1. They have been resolved and it did dissapear. I had to remember to delete the webparts out of the recycle bin :P

      Delete
  11. Hey Phil, in your syntax here:

    Run-SQLQuery -SqlServer "SQLSERVER" -SqlDatabase "SP2010_Content" -SqlQuery "SELECT * from EventReceivers where Assembly = ‘PAC.SharePoint.Tagging, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b504d4b6c1e1a6e5'" | select Id, Name, SiteId, WebId, HostId, HostType | Format-List

    The ‘ right before PAC causes a syntax error:

    Exception calling "Fill" with "1" argument(s): "Incorrect syntax near ','."
    At line:11 char:17
    + $SqlAdapter.Fill <<<< ($DataSet)
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    When I pasted it in the PowerShell ISE, it looks like a normal ' which I find strange, but when I took the quote out and added it back, the command finally worked even though it looked exactly the same, thought I was going crazy. Thought that might be interesting for any other lazy people like me who copy/paste and just change the environment variables. =)

    ReplyDelete
    Replies
    1. Thanks Jason, had the same problem here :)
      And thanks Phil of course for providing these scripts!

      Delete
  12. Is querying the content database with PowerShell supported?

    ReplyDelete
  13. Hi Phil - Thank you for the scripts.

    I could clean up some faulty webparts by using the script. But, some webparts are still showing up when I run test-spcontentdatabase .....

    When I go to the referring URLs (which got from the script), I don't see any webparts showing as error.

    In this case, how do we proceed? Please advise.

    Thanks!!

    ReplyDelete
  14. Hi Phil,

    I recently upgraded DB to SQL Server 2012, and the PowerPivot instance also. The powerpivotwebapp.wsp is installed and I see the webparts in the webpart gallery. But still there are MissingWebPart errors reported. I found the pages using above method, they are all system pages or list pages (NewForm.aspx, EditForm.aspx...)

    Removing webpart for these pages is not a solution here... can you please suggest an alternate solution to find what causing the error

    Thanks

    ReplyDelete
  15. Looks like Karim Zaki has the same idea... or ripped off you article. http://karimzaki.blogspot.com/2012/03/title-missing-server-side-dependencies.html

    ReplyDelete
  16. Hi Peter - it was ripped from mine (the article date gives it away anyway). Unfortunately it happens a lot, but you learn to live with them in the end

    ReplyDelete
  17. If you didn't change string in query for "$SqlAdapter.Fill($DataSet) | Out-Null" - you will always get one blank result ;).
    In my case i clean one webPart. Query return me Null, in search on database i can't find that web part. But test-spcontentdatabase return me missingwebpart again and again.

    ReplyDelete
  18. Hi I successfully removed quite a number of these using your solution.

    However one of the web parts is referenced in a site collection that was deleted a number of weeks ago. would anyone be able to advise how to remove it from the content database.

    ReplyDelete
    Replies
    1. I successfully ran the script on a missing assembly

      Id : 7ac37010-80b5-45e9-bbb2-bb6496c5abdf
      Name :
      SiteId : 8ebcbcbd-a487-4efc-bb7c-8ec0f0d4f505
      WebId : 38792fb7-7c12-42de-89be-6da0819d1f14
      HostId : 38792fb7-7c12-42de-89be-6da0819d1f14
      HostType : 1

      Id : d343d81c-e8ba-4a1c-8c47-9fb5cc436e8a
      Name :
      SiteId : 8ebcbcbd-a487-4efc-bb7c-8ec0f0d4f505
      WebId : 38792fb7-7c12-42de-89be-6da0819d1f14
      HostId : 38792fb7-7c12-42de-89be-6da0819d1f14
      HostType : 1

      Id : 63e768e9-98c3-4902-975b-94ec58f01c29
      Name :
      SiteId : 8ebcbcbd-a487-4efc-bb7c-8ec0f0d4f505
      WebId : f013d491-942f-4511-8f2d-f15c0699a44b
      HostId : f013d491-942f-4511-8f2d-f15c0699a44b
      HostType : 1

      Id : 667692a6-2992-47a2-b129-0323c9659339
      Name :
      SiteId : 8ebcbcbd-a487-4efc-bb7c-8ec0f0d4f505
      WebId : f013d491-942f-4511-8f2d-f15c0699a44b
      HostId : f013d491-942f-4511-8f2d-f15c0699a44b
      HostType : 1

      However how do I remedy when Host Type is 1 - Site?

      Delete
  19. This, too, was very helpful. Thanks!

    ReplyDelete
  20. Hi Phil Childs,

    this post is very useful,but my scenario is quit different i have deleted the some site now the problem URL show up the page of those deleted site now i need to remove an entire from the Data base i think please advise me how to do that

    ReplyDelete
  21. Hi,
    I have used your script and was able to get rid of missing assemblies which were carried forward when migrating from one farm to another farm. Good work.

    ReplyDelete
  22. I am new with sharepoint and my task now is to upgrade sharepoint 2007 to 2010 can u ply tell me how can I do it step by step?

    I started to make preupgradecheck in the sharepoint 2007 server
    and this is the result

    Potential Upgrade Blocking Issues
    Issue : This server machine in the farm does not have a 64 bit version of Windows Server 2008 SP2 or higher installed.
    Upgrading in-place to SharePoint 2010 requires a 64 bit edition of Windows Server 2008 SP2 or higher.
    If you are planning to perform an in-place upgrade to SharePoint 2010, please upgrade the server machines in your farm to a 64 bit edition of Windows Server 2008 SP2 or higher, or migrate the current content databases from this farm to a new farm with servers running 64 bit edition of Windows Server 2008 SP2 or higher. Before attempting to install or upgrade to SharePoint 2010, please ensure that you run the SharePoint 2010 pre-requisites installer beforehand to ensure you have the correct set of prerequisites and patches installed. For more information about this rule, see KB article 954770 in the rule article list at http://go.microsoft.com/fwlink/?LinkID=120257.

    Issue : Pending upgrade operation detected in this farm
    Pending upgrade is detected on this farm. Either upgrade has not been run, or has failed after a recent update of the SharePoint software.
    Run the SharePoint Products and Technologies Configuration Wizard to finish the pending upgrade. For more information about this rule, see KB article 954775 in the rule article list at http://go.microsoft.com/fwlink/?LinkID=120257.

    Issue : Unsupported SQL Server or unresponsive database
    The following SQL server(s) do not meet the minimal version requirement:
    • DataSource = My Server Name, Version = 9.0.5057.0, 64-bit Edition = No, Express Edition = No

    For information on the minimum required SQL Server versions and how to download them, see KB article 976215 in the rule article list at http://go.microsoft.com/fwlink/?LinkID=120257.

    ReplyDelete
  23. Phil,

    Thank you so much for this series of posts (this and 2 others).

    To anyone having trouble getting the PowerShell scripts to run, you can just hijack Phil's SQL query and run it in SQL Management Studio.

    Another hint: Although pages you find may no longer have the referenced web part on it...a version in its history definitely does. And once you delete the page's version history, you have to delete the history from every level of the recycle bin all the way out of the Site Collection recycle bin. Once you wipe the page's history from existence Big Brother-style, you'll get rid of those nasty web part references.

    ReplyDelete
    Replies
    1. thank you for your history hint! it was THE solution for me!

      Delete
  24. Hi Kyle - good advice, thanks for sharing. Pleased the scripts are helping you :-)

    ReplyDelete
  25. Here's a more sane way to get all event receivers for an entire site:

    $ers = $site | Get-SPWeb | ForEach { $_.Lists | ForEach {
    $_.EventReceivers | where {$_.Assembly.Contains("PublicKeyToken=f97a74c1f8ab0ec3
    ") } } }

    $ers | ForEach { $_.Delete() }

    Kaboom!!! Gooneskiiies

    ReplyDelete
  26. Hey Wardrop - Thanks for sharing. It probably is more sane if you wanted to remove that event receiver from every list in every site and you knew what you were doing, but the purpose of the article is to try and get people to understand what they are removing and why. I've seen a lot of messes in my time from "one-liners" where people don't understand the consequences - hence why Microsoft build a "WhatIf" parameter into most of their cmdlets!

    ReplyDelete
  27. Garrett Stephens16 October 2013 16:54

    Your work is greatly appreciated. I did not find the list for event host types (Missing Assembly Section) at the included URL. I did find a good list at the URL below.

    http://msdn.microsoft.com/en-us/library/hh631803(v=office.12).aspx

    ReplyDelete
  28. Here is the solution. Visit this blog & download the utility

    http://learn-sharepoint-2013.blogspot.in/2014/09/find-missing-web-part.html

    Gaurav Goyal

    ReplyDelete