Two SharePoint Scripts For those Annoying MissingSetupFile and MissingWebPart Errors

 One annoyance I have with SharePoint is that 3rd party solutions don't always clean themselves up gracefully.  For example, if you install some 3rd party .WSP, enable whatever features it provides, then decide you don't like it, uninstalling it may not be straight forward.  Ideally you would just disable the feature and retract the solution.  However, many  3rd party solutions leave bits and pieces of themselves even after uninstall.  Worse, these bits and pieces can show up in Health Analyzer reports and even prevent upgrades and migrations. Even worse, the Health Analyzer's suggestion for "fixing" these issues is to _reinstall the solution and re-enable the features_.  9 times out of 10, this is not what you want!  You often want to clean up the mess and get rid of the annoying errors, but SharePoint doesn't give you details on exactly where the corruption is. 

 I recently ran across these excellent scripts for doing just this sort of cleanup.  But, they don't go far enough.  I wanted one script to let me see where exactly the problem was, and optionally to delete the problem files altogether.  With that in mind, here are the scripts I came up with, and how to use them.

Find all your errors
Run this script from a SharePoint Admin Powershell to see all such errors in your farm:

get-spcontentdatabase | %{Test-SPContentDatabase $_}

Fixup MissingWebPart Errors

MissingWebPart errors can happen when a solution is removed, but a webpart it contains is still on a page somewhere.  SharePoint doesn't tell you _what page_, so finding it is a bit of a challenge and involves sleuthing in the SharePoint Content Database (ill advised unless you absolutely have to!)

This script queries all content dbs for a problem webpart and optionally lets you delete the file.  Here's how to use it.

• Copy and paste the script below to a file called fixup-missingwebpart.ps1
• Run the test script above.
• For any MissingWebPart error, copy the web part id from the error message
• run fixup-missingwebpart.ps1 -webpartid
• This will list all pages where the webpart is.  You can go to them and manually delete the webpart
  OR
• run fixup-missingwebpart.ps1 -webpartid -delete $true
  to delete the pages if you know they are not used anywhere.  This is not reversible except by restoring from backup, and bypasses the recycle bin, so be careful!

param($webpartid, $delete = $false)
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]
}
$dbs = get-spcontentdatabase
$dbs | %{
$db = $_
$db.Name
$files = Run-SQLQuery -SqlServer $db.Server -SqlDatabase $db.Name -SqlQuery "SELECT * from AllDocs inner join AllWebParts on AllDocs.Id = AllWebParts.tp_PageUrlID where AllWebParts.tp_WebPartTypeID = '$webpartid'" | select Id, SiteId, DirName, LeafName, WebId, ListId, tp_ZoneID, tp_DisplayName | sort -unique
$files | ?{$_.SiteId -ne $null} | %{
$file = $_
$site = get-spsite $file.SiteId
$web = $site.AllWebs | ?{$_.Id -eq $file.WebId}
$spfile = $web.GetFile([Guid]$file.Id)
$site.WebApplication.Url + $file.DirName + '/' + $file.LeafName + '?contents=1'
if($delete -eq $true){
$spfile.Delete()
}
}
}

Fixup MissingSetupFile Errors

MissingSetupFile errors can happen when a solution is removed, but a file it installed is still in a site somewhere.  Like the other, SharePoint doesn't tell you where exactly the file is, so finding it is a bit of a challenge and involves sleuthing in the SharePoint Content Database (ill advised unless you absolutely have to!)

This script queries all content dbs for a problem webpart and optionally lets you delete the file.  Here's how to use it.

• Copy and paste the script below to a file called fixup-missingsetupfile.ps1
• Run the test script above.
• For any MissingSetupFile error, copy the filename from the error message
• run fixup-missingsetupfile.ps1 -filename
• Note if you are careful, you can also use '%' as a wildcard.  For example:
  run fixup-missingsetupfile.ps1 -filename "KnowledgeLake/%.xml"
  Just check carefully that it only grabs files that you know you no longer need.
• This will list all locations of the file.  You can go to them and manually delete them
  OR
• run fixup-missingsetupfile.ps1 -filename -delete $true
  to delete the files if you know they are not used anywhere.  This is not reversible except by restoring from backup, and bypasses the recycle bin, so be careful!

param($filename, $delete = $false)
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]
}
$dbs = get-spcontentdatabase
$dbs | %{
$db = $_
$db.Name
$files = Run-SQLQuery -SqlServer $db.Server -SqlDatabase $db.Name -SqlQuery "SELECT * from AllDocs where SetupPath LIKE '%' +'$filename'" | select Id, SiteId, DirName, LeafName, WebId, ListId
$files | ?{$_.SiteId -ne $null} | %{
$file = $_
$site = get-spsite $file.SiteId
$web = $site.AllWebs | ?{$_.Id -eq $file.WebId}
$spfile = $web.GetFile([Guid]$file.Id)
$web.Site.WebApplication.Url + $spfile.ServerRelativeUrl
if ($delete -eq $true){
$spfile.Delete()
}
}
}

That's it!  These errors are annoying, but hopefully these scripts will make them a little easier to track down and handle.