For monitoring of our production servers we rely on Powershell, and consequently SQLPSX. We recently (last night) deployed our first Sql Server 2012 server into production, and apparently our custom powershell monitoring was less than happy with this. It was gassing on about ‘Error converting data type varchar to uniqueidentifier’ and such.
Luckily, the fix was pretty easy. In the SqlServer module for SQLPSX in the SQLServer.psm1 file, there is a reference to the various version 10 smo libraries:
try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}
try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop; $smoVersion = 10}
catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"; $smoVersion = 9}
try
{
try {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended" -EA Stop}
}
catch {Write-Warning "SMOExtended not available"}
I simply changed these references (make sure you get all three!) to reference version 11 and voila! Everything is back to normal.
try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}
try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop; $smoVersion = 10}
catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"; $smoVersion = 9}
try
{
try {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended" -EA Stop}
}
catch {Write-Warning "SMOExtended not available"}
I’ve not noticed any ill-effects (as of yet) to modifying these libraries. I’ve not tried modifying any of the versions in any of the other modules either, but I’m pretty confident that changing them would work fine. MS is extremely good and keeping their API’s backwards compatible.
Obviously, make sure you have the Sql Server 2012 Shared Management Objects (SMO) installed. You can download them in the Sql Server 2012 Feature Pack.
As always, change at your own risk. Worked for me, might not for you.
Sql Server Powershell 