Powershell Invoke-SQLCmd and SQLPS

Question

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?

Does the other developer have the same execution policy? That should really only matter if a script is being ran. If you are not able to run the invoke-sqlcmd from the powershell command line, are you sure you have the SQL management tools installed? — AutomatedOrder
Run this command to make sure you even have SQLPS installed: Get-Module –ListAvailable -name SQLPS — AutomatedOrder
I ran Get-Module –ListAvailable -name SQLPS and it does appear that SQLPS is available in my C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules — Riskworks
This is a similar issue on SO but does not seem to be resolved: stackoverflow.com/questions/31343611/… — Riskworks
We're having the same issue on a machine here. Everything seems to be installed properly. We have a working machine to compare to the non-working machine. On the working one, we see stuff in the 'ExportedCommands' column when we list available modules. On the non-working one, the SQLPS and SQLASCMDLETS modules appear, but the ExportedCommands column is empty. Everything appears identical between the two computers. — DustinA

Navid Golforoushan Navid Golforoushan · Accepted Answer · 2017-07-31T17:29:26

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

Powershell Invoke-SQLCmd and SQLPS

3 Answers