I ran into a interesting one recently where after a new install of SQL Server 2014 on Windows 2008r2 (I don’t think the issue is down those specific versions, as ive seen it noted on others), when trying to call SQLPS via SSMS or via the Import-Module would fail give the error:

import-module : The specified module ‘SQLPS’ was not loaded because no valid
module file was found in any module directory.
At line:1 char:1
+ import-module SQLPS -DisableNameChecking
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ResourceUnavailable: (SQLPS:String) [Import-Modu
le], FileNotFoundException
+ FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Comm
ands.ImportModuleCommand

Convert-UrnToPath : The term ‘Convert-UrnToPath’ is not recognized as the name
of a cmdlet, function, script file, or operable program. Check the spelling of
the name, or if a path was included, verify that the path is correct and try
again.
At line:1 char:53
+ &{[System.Console]::Title = ‘SQL Server Powershell’;Convert-UrnToPath
‘Server[@N …
+ ~~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Convert-UrnToPath:String) [], C
ommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

SQLPSFail

To resolve the issue, first find the path of SQLPS

Get-ChildItem SQLPS -Recurse

For me this resulted in: C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules

SQLPSFail2

Now check what paths PowerShell is checking for Modules:

$env:PSModulePath -split ';'

As you can see, for some reason I didn’t have the path (I double checked by logging into a SQL Server instance that was working with SQLPS and it was, so now we need to put it in!)

SQLPSFail3

Theres a few different ways you can add module paths to Powershell, some temporary, some custom yourself via the profile. For this instance we clearly need to have it known to every user and permanently. To do that you need to run:

$CurrentValue = [Environment]::GetEnvironmentVariable("PSModulePath", "Machine")
[Environment]::SetEnvironmentVariable("PSModulePath", $CurrentValue + ";C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules", "Machine")

You should now be able to load the module from anywhere.

Advertisements