Function to Import the SQLPS PowerShell Module or Snap-in

The SQL Server 2014 basic management tools have been installed on the Windows 8.1 workstation that’s being used in this blog article.

When attempting to import the SQLPS (SQL Server PowerShell) module on your workstation, you’ll be unable to import it and you’ll receive the following error message if the PowerShell script execution policy is set to the default of restricted:

import-sqlmodule1a

Import-Module : File C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.
At line:1 char:1
+ Import-Module -Name SQLPS
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : SecurityError: (:) [Import-Module], PSSecurityException
+ FullyQualifiedErrorId : UnauthorizedAccess,Microsoft.PowerShell.Commands.ImportModuleCommand

It takes a while for the previous error message to be generated and it’s almost like that’s one of the last steps that’s performed when attempting to import the SQLPS module so I’ll plan to check this early on in my function and warn the user as soon as possible.

I recommend setting the script execution policy to RemoteSigned, but you should read the about_Execution_Policies help topic before changing it.

import-sqlmodule3a

Once that’s done, you’ll be able to import the module but you’ll receive a warning message about unapproved verbs and the current location is changed. Both are very annoying:

import-sqlmodule2a

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.

You could write a block of code to work around this problem and add it to your scripts:

I’ve seen other variations of the previous code floating around the Internet, but notice my version uses the best practice of filtering left. No need to grab a list of all the modules on your machine with Get-Module -ListAvailable only to pipe them to Where-Object to filter them down to the SQLPS module. That’s inefficient when compared to filtering left by using Get-Module with the -Name and -ListAvailable parameters.

The problem with adding the previous block of code to all of your SQL related PowerShell scripts is then you’ll be adding redundant code to potentially thousands of scripts. Even worse is the nightmare of finding and modifying all of them if you ever need to change that block of code for some reason. Good luck with that or keep reading to learn a better way.

I also want it to load the SQL PSSnapin if the machine has the SQL Server 2008 or 2008 R2 management tools installed instead of the SQL Server 2012 or higher module:

I decided to write a function that would take care of all of that for me and by adding it to a script module, I can simple reference the function from any script that I write:

You’re probably thinking “That’s a lot of code just to load the module or snap-in”. Look at the bigger picture! It’s not that much when you consider how much space the help and verbose messages are taking up and since I’m only going to write this once, it’s actually a lot less code than hard coding just a few lines in hundreds or possibly even thousands of scripts. Not to mention when I need to modify this code, I go to one script module and modify it instead of having to locate and modify those few lines in each of those scripts.

If the SQLPS module or SQL snapin doesn’t exist on the machine, you’ll receive a warning:

import-sqlmodule4b

If the execution policy is restricted, this warning will be displayed almost immediately:

import-sqlmodule5a

Using the Verbose parameter allows you to see exactly what’s occurring otherwise no output is generated if no errors occur:

import-sqlmodule6a

Using verbose output is much better than inline comments because the user of the function can see the messages without having to open the function or script module as in this case and read through possibly thousands of lines of code to find your comments.

Running the same function on a machine with PowerShell version 2 and the SQL 2008R2 client tools loads the SQL PSSnapin and it also loads the SMO (SQL Server Management Objects) .NET assembly:

import-sqlmodule7a

Why? Because I frequently use PowerShell with SMO and I can’t ever remember how to load the necessary assembly since this occurs automatically when using the PowerShell module that’s included with the SQL Server 2012 and higher management tools.

The Import-MrSqlModule shown in this blog article is part of my MrSQL PowerShell module which can be downloaded from GitHub.

µ

3 Comments

  1. Robert Sweetman

    This is literally the best explanation of this topic on the internet. I have been smacking my head on the brick wall of sqlps for a week which has to be working on a set of VM’s for our test system to work at all.

    Reply
  2. Andrew Loree

    While this works beautifully on most every SQL Server version I have needed, ran across an issue when trying to run the SMO Scripter. One some machines, using the above module loading method, I would get type errors when trying to run the Scripter.script() method on objects.

    Only solution I found was to NOT explicitly load SMO, but instead load the SQLPS module as shown for SQL 2016 MSDN page, which appears to work with clients that have 2012 and 2014 SQLPS installed – https://technet.microsoft.com/en-us/library/cc281962(v=sql.105).aspx#Anchor_5

    #
    # Add the SQL Server Provider.
    #

    $ErrorActionPreference = “Stop”

    $sqlpsreg=”HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps”

    if (Get-ChildItem $sqlpsreg -ErrorAction “SilentlyContinue”)
    {
    throw “SQL Server Provider for Windows PowerShell is not installed.”
    }
    else
    {
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
    }

    #
    # Set mandatory variables for the SQL Server provider
    #
    Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
    Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
    Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
    Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

    #
    # Load the snapins, type data, format data
    #
    Push-Location
    cd $sqlpsPath
    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100
    Update-TypeData -PrependPath SQLProvider.Types.ps1xml
    update-FormatData -prependpath SQLProvider.Format.ps1xml
    Pop-Location

    Reply

Leave a Reply

%d bloggers like this: