SharePoint HealthReport - Missing Assembly

This is part of a series on SharePoint Health Report issues, because I can never remember what to do about them.

Posts in this series

• Claims/Classic mismatch
• Missing WebPart
• Missing Assembly
• Missing Feature

Missing Assembly

The warning looks like this:

Category        : MissingAssembly
Error           : True
UpgradeBlocking : False
Message         : Assembly [Microsoft.Office.Access.Server.Application, Version=16.0.0.0, Culture=neutral,
                  PublicKeyToken=71e9bce111e9429c] is referenced in the database [WSS_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 [WSS_Content],
                  but are not installed on the current farm. Please install any
                  feature or solution which contains these assemblies.

The solution here is either to install the missing assembly - or remove the reference fully from the ContentDB. Missing Assembly errors are always connected to registered EventReceivers and can thus be easily found in the database:

#Requires -PSSnapin Microsoft.SharePoint.PowerShell
<#
  .SYNOPSIS
	Remove EventReceivers from the contentDB
	adapted from https://www.sharepointdiary.com/2016/01/fix-missingassembly-error-test-spcontentdatabase-in-sharepoint-migration.html#:~:text=Root%20Cause%3A,the%20SharePoint%20list%20or%20libraries!

  .EXAMPLE
	.\Remove-SPEventReceiverFromContentDB.ps1 -Assembly "Microsoft.Office.Access.Server.Application, Version=16.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" -ContentDb WSS_Content -WhatIf
	Reports on whether the EventReceivers of the assembly Microsoft.Office.Access.Server.Application are used in any of the sites in WSS_Content
#>

[CmdletBinding(SupportsShouldProcess)]
param(
	[Parameter(Mandatory=$true)]
    [string]$Assembly,
	[Parameter(Mandatory=$true)]
    [string]$ContentDb
)
$ErrorActionPreference="Stop"

Function Run-SQLScript($SQLServer, $SQLDatabase, $SQLQuery)
{
    $ConnectionString = "Server =" + $SQLServer + "; Database =" + $SQLDatabase + "; Integrated Security = True"
    $Connection = new-object system.data.SqlClient.SQLConnection($ConnectionString)
    $Command = new-object system.data.sqlclient.sqlcommand($SQLQuery,$Connection)
    $Connection.Open()
    $Adapter = New-Object System.Data.sqlclient.sqlDataAdapter $Command
    $Dataset = New-Object System.Data.DataSet
    $Adapter.Fill($Dataset)
    $Connection.Close()
    $Dataset.Tables[0]
}

$spContentDb = Get-SPContentDatabase $ContentDB

#Query SQL Server content Database to get information about the MissingAssembly
$Query = "SELECT distinct Id, SiteId, WebId, HostId, HostType from EventReceivers where Assembly = '$($Assembly)'"
$QueryResults = Run-SQLScript -SQLServer $spContentDb.Server -SQLDatabase $spContentDb.Name -SQLQuery $Query

$ReportOnly = !$PSCmdlet.ShouldProcess($Assembly)

#Iterate through results
foreach ($Result in $QueryResults)
{
    if($Result.id -ne $Null)
    {
		$EventReceiver = $null

        #Get the location where the event receiver is referred
		switch ($Result.HostType)
		{
			0 {
				$Site = Get-SPSite -Limit all | where {$_.Id -eq $Result.SiteId}
				$EventReceiver = $Site.EventReceivers | where {$_.Id -eq $Result.Id}
				write-host "Site Event Receivers Found at $($Site.URL)" -foregroundcolor green
			}

			1 {
				$Site = Get-SPSite -Limit all | where {$_.Id -eq $Result.SiteId}
				$Web = $Site | Get-SPWeb -Limit all | where { $_.Id -eq $Result.WebId }
				$EventReceiver = $Web.EventReceivers | where {$_.Id -eq $Result.Id}
				write-host "Web Event Receivers Found at $($web.URL)" -foregroundcolor green
			}

			2 {
				$Site = Get-SPSite -Limit all | where {$_.Id -eq $Result.SiteId}
				$Web = $Site | Get-SPWeb -Limit all | where { $_.Id -eq $Result.WebId }
				$List = $web.Lists | where {$_.Id -eq $Result.HostId}
				$EventReceiver = $List.EventReceivers | where {$_.Id -eq $Result.Id}
				write-host "List Event Receivers Found at $($web.url)/$($List.RootFolder.Url)" -foregroundcolor green
			}

			Default { Write-Host "Unknows HostType: $($Result.HostType)!" }
		}

		if($EventReceiver -ne $null -and -not $ReportOnly) {
		  $EventReceiver.Delete()
		}
    }
}