Loading SMO assemblies

Before you can use the SMO library, the assemblies need to be loaded. With the introduction of the SQLPS module, this step is easier than ever.

Getting ready

In this recipe, we assume you have already installed SMO on your machine.

How to do it...

To load SMO assemblies via the SQLPS module, perform the following steps:

Open up your PowerShell console, PowerShell ISE, or your favorite PowerShell Editor.
Type the import-module command as follows:
```
Import-Module SQLPS
```
Confirm that the module is loaded by running the following. This should give the name of the module if it is loaded:
```
Get-Module
```

How it works...

The way to load SMO assemblies has changed between different versions of PowerShell and SQL Server. Before the SQLPS module and in PowerShell v1, loading assemblies could be done explicitly using the Load() or LoadWithPartialName() methods. The LoadWithPartialName() accepts the partial name of the assembly and loads from the application directory or the Global Assembly Cache (GAC):

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

Although you may still see LoadWithPartialName() in some older scripts, this method is now obsolete and should not be used with any new development.

The method Load() requires the fully qualified name of the assembly:

 [void][Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

In PowerShell v2, assemblies can be added by using Add-Type:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo"

When the SQLPS module was shipped with SQL Server 2012, loading these assemblies one by one became unnecessary, as long as the SQLPS module is loaded using the following code:

Import-Module SQLPS

There may be cases where you will still want to load specific DLL versions if you are dealing with specific SQL Server versions. Alternatively, you might want to load only specific assemblies without loading the whole SQLPS module. In this case, the Add-Type command is still the viable method of bringing the assemblies in.

There's more...

When you import the SQLPS module, you might see an error about conflicting or unapproved verbs:

Note

WARNING: The names of some imported commands from the module SQLPS include unapproved verbs that might make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose parameter.

For a list of approved verbs, type Get-Verb.

This means there are some cmdlets that do not conform to the PowerShell naming convention, but the module and its containing cmdlets are still all loaded into your host. To suppress this warning, import the SQLPS module with the –DisableNameChecking parameter.

Note

Learn how to load SMO assemblies using PowerShell from the MSDN at https://msdn.microsoft.com/en-us/library/hh245202(v=sql.120).aspx.

SQL Server 2014 with Powershell v5 Cookbook

By : Donabel Santos

SQL Server 2014 with Powershell v5 Cookbook

By: Donabel Santos

Overview of this book

Loading SMO assemblies

Getting ready

How to do it...

How it works...

There's more...

Note

Note

See also

SQL Server 2014 with Powershell v5 Cookbook

By : Donabel Santos

SQL Server 2014 with Powershell v5 Cookbook

By: Donabel Santos

Overview of this book

Loading SMO assemblies

Getting ready

How to do it...

How it works...

There's more...

Note

Note

See also

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access