Powershell Invoke-SQLCmd and SQLPS

powershell import-module sqlps executionpolicy
Riskworks picture Riskworks · Oct 16, 2015 · Viewed 23.1k times · Source

Windows 7 workstation POSH 3.0 SS 2012 SP1

Abstract:
Developer 1 cannot run Invoke-SQLCmd
Developer 2 with similar SS Client tools installation can run Invoke-SQLCmd
Developer 1 cannot run Import-Module SQLPS

When I try to run a query from my laptop like the following:

Invoke-sqlcmd -ServerInstance <ServerName> -Query "Select top 10 * from <SomeTable>;"

I get the following error:

Invoke-sqlcmd : The 'Invoke-sqlcmd' command was found in the module 'SQLPS', 
but the module could not be loaded. For more information, run 'Import-Module 
SQLPS'.

Yet another developer can successfully run the command, and we both have a local

I cannot run Import-Module sqlps on my machine for obvious reasons - ExecutionPolicy is Restricted.

I am not sure why the other developers machine allows the query, while mine does not. Is this a SQL Client install issue?

Answer

Navid Golforoushan picture Navid Golforoushan · Jul 31, 2017

It is all about Microsoft. They have the best technology but not simply one way and straight path to use their tool. :( You can use SMO object to Interact with your database. (You can use it with C#/VB.net/PowerShell) :$ You can use SQLPS You can use Invoke-SQLCmd

Just Run below Script to make sure feature pack installed in your machine and Use SQLPS which is easier to use than other solution (personal preference)

Install-module -Name SqlServer -Scope CurrentUser

# Import the SQL Server Module.    
Import-Module Sqlps -DisableNameChecking;

# To check whether the module is installed.
Get-Module -ListAvailable -Name Sqlps;
cd SQLSERVER:\SQL

Related questions

PowerShell 4 - Import-Module : The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory

Previously working with PowerShell 3. Upgraded to PowerShell 4 and uninstalled it as facing error stated. Import-Module : The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory. But all SQL related stuff is working …

Read More
What's the difference between Add-PsSnapIn and Import-Module

Powershell has two means for importing additional cmdlets into a session, Add-PsSnapIn and Import-Module. The docs are not at all clear regarding when one would chose one over the other. Does anybody know the difference between these?

Read More
Editing an already imported module

Before importing my powershell module (MyModule.psm1), I have written one function in it: Function T1() { Write-Host "T1 is just called" -ForegroundColor red } In my MyModule.psd1: @{ PowerShellVersion = '2.0' PowerShellHostName = '' PowerShellHostVersion = '2.0' RequiredModules = @() ScriptsToProcess = @() NestedModules = @() FunctionsToExport = '*…

Read More