I’ve been using SQLPS as part of a script developed by my colleague Carol which needs to be invoked both interactively and also from a MIM workflow activity.  I ran into problems this week trying to use the SQL2016 feature pack.

I simply figured I should be using the latest platform for my Win2016 MIM platform so didn’t hesitate to install it in preference to the last one I used.  The script worked fine with the Import-Module approach when run interactively, but drops into the add-pssnapin sqlserverprovidersnapin120 alternative when this fails when invoked by the MIM service (due to a known .Net version compatibility issue –  and by the way, 130 corresponds to SQL2016):

try {Import-Module "sqlps" -DisableNameChecking}
catch
{
    if(@(get-pssnapin | where-object {$_.Name -eq “sqlserverprovidersnapin130”} ).count -eq 0) {add-pssnapin sqlserverprovidersnapin130}
    if(@(get-pssnapin | where-object {$_.Name -eq “sqlservercmdletsnapin130”} ).count -eq 0) {add-pssnapin sqlservercmdletsnapin130}
}

The second add-pssnapin call above was failing with a “this assembly is built by a runtime newer than the currently loaded runtime” exception … and so I rolled back to SQL2014 (120) and it worked like a charm:

try {Import-Module "sqlps" -DisableNameChecking}
catch
{
if(@(get-pssnapin | where-object {$_.Name -eq “sqlserverprovidersnapin120”} ).count -eq 0) {add-pssnapin sqlserverprovidersnapin120}
if(@(get-pssnapin | where-object {$_.Name -eq “sqlservercmdletsnapin120”} ).count -eq 0) {add-pssnapin sqlservercmdletsnapin120}
}

So obviously something is changing in this space for this not to work … but until I have an alternative that works for SQL2016 you may want to roll back to SQL2014 like me.

For future reference, the following registration steps were necessary after the SQL2014 feature pack install.

• download and install (as admin) each of the following from the download collection:
  • SQLSysClrTypes.msi
  • SharedManagementObjects.msi
  • PowerShellTools.msi
• run the following to register the DLLs (I ran this as a script saved locally to the SQLPS program files folder):

[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")           
$publish = New-Object System.EnterpriseServices.Internal.Publish           
$publish.GacInstall(“Microsoft.SqlServer.Management.PSProvider.dll”) | Out-String
$publish.GacInstall(“Microsoft.SqlServer.Management.PSSnapins.dll”) | Out-String

Set-Alias installutil C:\windows\Microsoft.NET\Framework64\v2.0.50727\InstallUtil.exe
installutil -i "C:\Program Files\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSProvider.dll"
installutil -i "C:\Program Files\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll"
Get-PSSnapin -Registered