THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Loading SMO Assemblies into PowerShell

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

Published Sunday, December 07, 2008 4:59 PM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Gordon said:

You can send the output of LoadWithPartialName to null if you wish instead of to the console.  I've found it necessary just to avoid "output clutter"...

December 8, 2008 10:34 AM
 

AllenMWhite said:

Gordon, you can send the output to null, and in the conditional part of the code I show here I do just that, but I need to know what version of the SMO libraries are being loaded, and I need the output of the load to determine that.

December 8, 2008 3:00 PM
 

Gordon said:

Ah, understood.

I've not run into version issues, and have run my same scripts against multiple versions.  

Have you run into something that requires a version check?

December 8, 2008 4:32 PM
 

AllenMWhite said:

When you have some servers running SQL 2008 and others running SQL 2005, and are running the same backup scripts for both, you need logic like this because of the change in the DLLs between the two versions.  I've written a number of scripts that ran fine from systems where SQL 2005 was installed, yet failed miserably on systems where SQL 2008 was installed.  This allows the script to run with either version.

I hope this makes sense.  In either case the scripts will run against servers running all versions of SQL Server (back to SQL2000, that is), but it's what's loaded on the server running the script that makes a difference.

December 8, 2008 9:40 PM
 

Mike L said:

This helped me out tremendously.  I've begun to test SQL 2008 Express 2008 and notice my PowerShell backup script was failing on the SMO library.  It worked on SQL 2005 Express and I hadn't had time to look into.

Bravo!

December 9, 2008 4:04 PM
 

Niels Grove-Rasmussen said:

Allen - you have just saved my day!

Great blog entry; both problem and solution in detail.

December 18, 2008 7:27 AM
 

jmblock said:

try this:

$v = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

if ( $v.GetName('version') | where {$_.version -like '10*'})

{

"some block of code here"

}

May 14, 2009 2:09 PM
 

some guy said:

Thanks Allen, I'm glad I came across this or the script I'm working on now, which needs to work on sql server 2005 and 2008, may have blown up. Here's how I implemented:

$assemblyInfo = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

if ($assemblyInfo.GetName().Version.Major -ge 10)

{

# sql server version is 2008 or later, also load these other assemblies

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null

}

August 3, 2010 3:49 PM
 

DBA said:

Thanks.

I am using ScriptTransfer() of SMO to script the databases and only on a SQL2008 R2 it is failing with the following error. Other versions including 2012 work fine with the same powershell code.

"scripttransfer" with "0" argument(s): "script transfer failed. "

May 15, 2013 3:43 PM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement