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