Microsoft included PowerShell with SQL Server 2008, and the new sqlps.exe program which includes the SQL PowerShell drivers including the PS Drives and new cmdlets for SQL Server. I prefer to work from vanilla PowerShell and load the assemblies I need, so I have code at the start of my script to load the appropriate assemblies.
When I started testing my SQL 2005 PowerShell scripts against a system with SQL 2008 loaded I ran into problems because many of the functions formerly included in the SMO DLL were moved to the SMOExtended and SQLWMIManagement DLLs. I was teaching a class this past week where a student asked if the code could be written so that the proper DLLs were loaded regardless of which version of SQL Server was installed.
I started playing with it and found some anomalies (or what seemed to me to be anomalies) in doing this. First, I expected the version information passed back when you load an assembly to be different between the two systems, but when I loaded the SMO assembly both reported back v2.0.50727. This is obviously of no help.
After playing with a few properties I found the release information I wanted buried in the property called FullName. I executed the following statement:
$v = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') The $v variable contains a value of type System.Reflection.Assembly and the FullName property of this variable contains
2005: Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 2008: Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
So, by splitting out the string returned by the FullName property I can get the version. First I split by a comma (,), then by the equal sign (=), then finally by a period (.), and when I'm done I have the main version number of the DLL loaded. Here's the code:
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries $v = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') $p = $v.FullName.Split(',') $p1 = $p[1].Split('=') $p2 = $p1[1].Split('.') if ($p2[0] -ne '9') { [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null } With this at the head of my PowerShell scripts I can be certain that whatever version of SQL Server I'm using the same scripts will load and run properly.
Allen
Source Click Here.
Post a Comment