How to run AdamJ's WSUS Automated Maintenance script on Server 2016 Core

My WSUS server runs Server 2016 Core edition without a GUI so everything must be installed via powershell. When I run Adamj_Clean-WSUS.ps1 -FirstRun the errors are:

The term 'sqlcmd' is not recognized...

Executing ComputerObjectCleanup
Executing WSUSDBMaintenance
11     Job11           BackgroundJob   Failed        False           localhost            sqlcmd -S np:\\.\pipe\...
The term 'sqlcmd' is not recognized as the name of a cmdlet...

You cannot call a method on a null-valued expression.
At C:\Users\user\Documents\Adamj_Clean-WSUS.ps1:2480 char:9
...
You cannot call a method on a null-valued expression.
At C:\Users\user\Documents\Adamj_Clean-WSUS.ps1:2482 char:9
+         $Script:AdamjWSUSDBMaintenanceOutputHTML += $AdamjWSUSDBMaint ...
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Executing WSUSServerCleanupWizard
_

Had to kill the window to stop this after it ran for several hours.

Solution

Download the ODBC driver and the SQL Command Line Utilities

https://learn.microsoft.com/en-us/sql/connect/odbc/windows/system-requirements-installation-and-driver-files#installing-microsoft-odbc-driver-for-sql-server

https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility

Copy files to the core server

One method is to place them into a share on another server then mount that share:

New-PSDrive -Name T -PSProvider FileSystem -Root \\<server_IP>\<share_name> -Credential <domain>\<user>
cp T:\<folder>\msodbcsql.msi.msi .
cp T:\<folder>\MsSqlCmdLnUtils.msi .
cp T:\<folder>\Clean-WSUS.ps1 .

How to install msi files

If you're used to hitting the tab key for filename completion and think a ".\" automatically prepended to a filename is innocent, be aware it is not: https://superuser.com/a/1310787/891171

Install odbc driver

msiexec /i msodbcsql.msi /qn IACCEPTMSODBCSQLLICENSETERMS=YES /L*V msodbcsql.log

verify log:

Get-Content -path .\msodbcsql.log

Install sqlcmd

msiexec /i MsSqlCmdLnUtils.msi /Passive /NoRestart IACCEPTMSSQLCMDLNUTILSLICENSETERMS=YES /L*V MsSqlCmdLnUtils.log

verify log:

Get-Content -path .\MsSqlCmdLnUtils.log

Set path

This is where my SQLCMD.EXE was installed. YMMV:

$env:Path += ";C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\"

Execute the script

As noted within the script (you did read the comments and set your variables already, right?), run this first:

PowerShell.exe -ExecutionPolicy Bypass

Then:

Clean-WSUS.ps1 -FirstRun