Tuesday, August 29, 2017

Why can I not use SQLPS to run this?

Here's the scenario:

I have a script, loaded into C:\temp\checkps.ps1, on a remote dev SQL Server, whose execution policy has been set to unrestricted. (Because you wouldn't allow that on a Prod server, would you?)

Let's say that file has the following body:

 Import-Module Sqlps -DisableNameChecking  
 Get-Module -ListAvailable -Name Sqlps  

(Incidentally, that's from this MS docs page)

I want to execute something like this in SSMS:

 Exec xp_cmdshell 'powershell.exe -file c:\temp\checkps.ps1 -ExecutionPolicy Unrestricted'  

And instead, I got this error:

invoke-sqlcmd is not recognized in windows powershell
The term 'invoke-sqlcmd' 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.
Import-Module : The specified module 'Sqlps' was not loaded because no valid

"Import-Module : The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory."
also, got this

Invoke-Sqlcmd : The term 'Invoke-Sqlcmd' 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:1


So, I wound up having to do three things.

First, I downloaded and ran the Powershell extensions installer on the server itself.

Second, to copy the SQLPS module directory from where it was, to here:

C:\Windows\system32\WindowsPowerShell\v1.0\Modules

Note that I first had to get the location of SQLPS, as in running this in poweshell:

$env:PSModulePath


Third, I had to grant the service account full control of the script location through Windows right click menu (Properties >> Security >> Add >> Add user account >> Full Control )











No comments:

Post a Comment