Add-Type different behavior when loading SQL SMO(2k5/2k8) assemblies…

Another sample of why understanding Microsoft .NET Framework can help extend your PowerShell scripting.  Using SQL Server Management Objects (SMO) can give both SQL Administrators and SQL Developer the tools to manage their SQL environment.  So, been myself a IT/SQL Developer, I recently discover a strange behavior with the PowerShell V2 Add-Type Cmdlet that got me puzzle.  Of course, I may have cause this behavior to happened because my Developer/Presenter laptop is very customized with a lot of tools.  So, let get to it.

Reminder, to work with SMO you need to install  Feature Packs SQL Server Management Objects for 2005/2008 or just install SQL Server 2008 (R2):

1. For SQL Server 2005 SMO only – Feature Pack for Microsoft SQL Server 2005 – November 2005 – ”Microsoft SQL Server 2005 Management Objects Collection”: http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en#filelist

2. For SQL Server 2008 SMO Only – Microsoft SQL Server 2008 Feature Pack, October 2008 – ”Microsoft SQL Server 2008 Management Objects”http://www.microsoft.com/downloads/details.aspx?FamilyID=228de03f-3b5a-428a-923f-58a033d316e1&DisplayLang=en 

3. Included in both SQL Server SQL 2008 and 2008 R2 installation.  The SMO feature can be install on machines that don’t have any SQL Server installed.

 

While reworking one of my PS SQL script, I was replacing all my line that have the “[System.Reflection.Assembly]::LoadWithPartialName(xxx)” with the Add-Type when I encounter an error running the script:

Somehow the Add-Type Cmdlet was trying to load version 9 of the SMO, which belong to SQL Server 2005.  Not to long ago, I decide to remove some of my SQL Servers 2005 instances, keep SQL 2008, and installed other SQL Server versions throughout my virtual machines.  Apparently, the SQL Server 2005 uninstall process may have left some things behind. If you want load the SMO assembly, expecting to be Version 10 – for SQL Server 2008, use the following line:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")  # – This will work

What’s the resolution to this problem?

I can continue using the .NET System.Reflection because it will always work on any of the available PowerShell versions.  But, how can I use the Add-Type Cmdlet provided in V2?  For me, in order to find the way to update my scripts, had to use the [System.Reflection.Assembly]::LoadWithPartialName() to load all my Assemblies I normally.  Then, I use the following .NET method “AppDomain.GetAssemblies( )” to get all of my loaded assemblies in my PowerShell session.  This way I can create a variable where I can find the full path of the assembly I want to load.

Now, through past experiences and searching on the internet, I know that sometime you may want load the assembly (or dll’s) using the full pathname of where is located in order to load the assembly correctly.  For more information about using [System.Reflection.Assembly]::LoadWithPartialName() or ::LoadFile(), go here: http://msdn.microsoft.com/en-us/library/system.reflection.assembly.loadwithpartialname.aspx   and http://msdn.microsoft.com/en-us/library/system.reflection.assembly.loadfile.aspx

The following steps will show how to list all your loaded assemblies, get the full pathname(to be added to the “Add-Type –Path …“, and use the Add-Type to finally run the desired assembly “Microsoft.SqlServer.Smo:

Add-Type -Path "C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll"

Here’s the results:

Here’s the code:

# - List all your Assemblies on your System
[System.AppDomain]::CurrentDomain.GetAssemblies()

# - Save results into a variable
$x = [System.AppDomain]::CurrentDomain.GetAssemblies()

# - You only need to "Location" to load the full assembly
$x | select Location

Add-Type -Path "C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll"

$MySQL1 = new-object('Microsoft.SqlServer.Management.Smo.Server') "YourSQLName"
$MySQL1.Information

Now, this scenario may be an exception. but if you find yourself in the same spot, you will be able to either: to go around this issue or just keep using the .NET method  [System.Reflection.Assembly]::LoadWithPartialName(). 

Happy PowerShell Scripting!!

Advertisements

About maxt2posh

I’m from Puerto Rico, have been working with computers since 1979. Throughout many years, I have worked with SQL Server Technologies, provided support to Windows Servers/Client machines, Microsoft Virtualization Technologies, and build some Visual Studio solutions. I’m a Microsoft PowerShell MVP since 2009 and MVP SAPIEN Technologies since 2010. I speak in most of the SQLSaturday, IT Pro and .NET camps events around the Florida’s State. Also founder of the Florida PowerShell User Group which meeting every 3rd Tuesday evening of the month.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s