Invoke-Sqlcmd - "Mixed mode assembly is built against version 'v2.0.50727' of the runtime” and HP Server Automation

I got this error message when trying to run the Invoke-Sqlcmd powershell cmdlet against a server remotely using HP Server Automation (HPSA).  The full error message is:

Invoke-Sqlcmd : Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

If you search the error message online, you’ll discover some solutions like this, which involve editing XML configuration files.  As HPSA doesn’t use the regular powershell configuration files, I got stuck for a while trying to figure out which configuration file it does use.

As it turns out, the configuration file that HPSA uses is stored (by default) at the following location:  C:\Program Files\Opsware\agent\bin\PsSasHost.exe.config The solution is to edit the configuration file to include the bolded section:

1<?xml version="1.0" encoding="utf-8" ?>
2<configuration>
3  <startup <strong>useLegacyV2RuntimeActivationPolicy="true"</strong>>
4    <supportedRuntime version="v4.0"/>
5    <supportedRuntime version="v2.0.50727"/>
6  </startup>
7</configuration>

You can also add the following code to a powershell script, which will make the necessary edits to the config file on the server.  This script will have to be run prior to the script which runs the Invoke-Sqlcmd commands.

1$ConfigFile = "C:\Program Files\Opsware\agent\bin\PsSasHost.exe.config"
2$Xml = [xml](Get-Content -Path $ConfigFile)
3$Xml.configuration.startup.SetAttribute("useLegacyV2RuntimeActivationPolicy","true")
4$Xml.Save($ConfigFile)

Problem solved!

N.B. The problem with the SQLPS module which causes this error to pop up is fixed in SQL 2016.  However, the 2016 SQLPS module comes with its own issues.  As has been documented in official bug reports (1, 2), installing the SQL 2016 version of the SQLPS module will break backwards compatibility with SQL 2014 instances even if you don’t load the 2016 SQLPS module.  Apparently, the 2014 SQLPS module exhibits the same behaviour when trying to manage SQL 2012 instances.  Well done Microsoft!