Monday, October 3, 2016

SQL SERVER – syspolicy_purge_history job failing step

The corresponding line is 'import-module SQLPS  -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'File C:\Windows\system32\WindowsPowerShell\v1.0\Modules\SQLPS\Sqlps.ps1 cannot be loaded because the execution of scripts is disabled on this system

Import-Module SQLPS Fails on a SQL Server instance

Try importing SQL module from Powershell

PS > import-module SQLPS - DisableNameChecking

import-module: The specified module SQLPS was not loaded because no valid module file was found if any module directory.

The error is because by default powershell will try to check the module with the default powershell module folder i,e., $env:PSModulePath

$env:PSModulePath -- Returns the current powershell module path

However the SQLPS will exists on E:\Program Files(x86)\Microsoft SQL Server\110\Tools\Powershell\Modules so we need to attach this path to PSModulePath in environment variables which will allow us to import the SQLPS Module.