Friday, May 30, 2014

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.

11 comments:

TechNoodles said...

Hi, I am hoping you can help me out. I get this error message but believe no -webpartid is provided in the error.
Category : MissingSetupFile
Error : True
UpgradeBlocking : False
Message : File [Features\RemoveRecent_RemoveRecent\Style
Library\js\HideRecent.js] is referenced [1] times in the
database [WSS_Content], but is not installed on the current
farm. Please install any feature/solution which contains
this file.
Remedy : One or more setup files are referenced in the database
[WSS_Content], but are not installed on the current farm.
Please install any feature or solution which contains these
files.
Locations :

TechNoodles said...

To add a little more context: I did add the removerecent codeplex solution. https://rr.codeplex.com/. I did not add as a sandbox solution rather I globally deployed it because I didn't know what I was doing. I did activate the feature on a site. Then, I reversed my steps. Deactivated the feature, uninstalled and removed feature. Now, I get an error message in Central Admin

Title: Missing server side dependencies.
Severity: 1 - Error
Category: Configuration
Explanation: [MissingSetupFile] File
[Features\RemoveRecent_RemoveRecent\Style Library\js\HideRecent.js] is referenced [1] times in the database [WSS_Content], but is not installed on the current farm.

Daniel Root said...

The second script should tell you where exactly it's referenced (you don't use webpartid in the second script, just part of the path). Then you can decide if you want to force delete with the -delete parameter or manually delete the site (don't forget to delete from recycle bin and site collection recycle bin if so)

TechNoodles said...

Thanks. You mentioned "manually delete the site". Do I have to delete the "site" to get rid of the reference? I have poked around in the SQL tables and cannot find a reference to "HideRecent.js".

Daniel Root said...

Often when this happens, it's on a test site or a site that is no longer used. For example, somebody wants to try WebPartXYZ, sets it all up, doesn't like it and forgets about it. In that case, it's easier and better to delete the site completely. If you still need the rest of the site, you'd try to just delete the offending file.

The second script would run SELECT * FROM AllDocs WHERE SetupFile LIKE '%HideRecent.js' against WSS_Content. (Note: It's generally safe to SELECT, but don't be tempted to delete or modify the records in any SP db- always use SP UI or powershell) If you're sure HideRecent.js is not there, then I'm not sure why it's being reported in CA. You might try re-running the Health Analyzer check in that case.

Unknown said...

Thanks ! !

Anonymous said...

I was hoping you could help me, I ran the WebParts script, and got the following errors on all the content databases in the site.
Exception calling "Fill" with "1" argument(s): "Conversion failed when
converting from a character string to uniqueidentifier."
At C:\Scripts\fixup-missingwebpart.ps1:12 char:5
+ $SqlAdapter.Fill($DataSet)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException

I know there are missing web parts from running the Test-SPContentDatabase, but am having trouble identifying where they still exist.

ABE said...
This comment has been removed by the author.
David P said...

I had to remove the | sort -unique
on the line that makes the SQL call for the script to work.

Thanks. Script helped with our cleanup.

nullldata said...
This comment has been removed by the author.
nullldata said...

"Get-SPContentDatabases" will not list the content databases that are set with an "Offline" status.

Instead, you can replace "Get-SPContentDatabases" with the following to list the offline databases:

$webApplication = Get-SPWebApplication "https://YOURWEBAPPLICATION"

$webApplication.ContentDatabases