Thursday, February 14, 2019

[EPR] Making the Extensible Powershell Repository work on a box with SQL Server 2017 since add-sqltable fails with Microsoft.SqlServer.Dmf

We're migrating the EPR (http://thebakingdba.blogspot.com/2013/04/servers-extensible-powershell.html) to a new box.  After 6 years, and it's still one of my best tools.  (Just drop a SQL or PS1 script in a folder, and it'll create a table and run it against all your servers).

Alas, Chad Miller's excellent add-sqltable started failing.  To the internet!

Could not load file or assembly 'Microsoft.SqlServer.Dmf"

Looking around, it appears that it's a problem with SMO.  On 2017, they split out SMO and SSMS.  Which means that installing SSMS 2017 doesn't get you SMO.  Supposedly installing it (install-module sqlserver) will do that, but I'd already did that and it wasn't working.

So, after fighting with it for a little bit, I decided to "get it working" and revisit later.

1) Install SSMS 2016 (which is when they started separating out SSMS)
2) Change the version from 11 to 13 in the script, as below: 

try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}

try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop} 
catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"} 

No comments: