5

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?

AutomatedOrder
  • 501
  • 4
  • 14
Riskworks
  • 201
  • 2
  • 3
  • 9
  • 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 Oct 16 '15 at 14:53
  • Run this command to make sure you even have SQLPS installed: `Get-Module –ListAvailable -name SQLPS` – AutomatedOrder Oct 16 '15 at 15:19
  • 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 Oct 16 '15 at 15:24
  • This is a similar issue on SO but does not seem to be resolved: http://stackoverflow.com/questions/31343611/invoke-sqlcmd-not-recognized-even-though-sqlps-loaded?rq=1 – Riskworks Oct 16 '15 at 15:28
  • 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 Oct 22 '15 at 21:30

3 Answers3

5

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
Navid Golforoushan
  • 728
  • 1
  • 9
  • 16
  • 3
    Modules: SQLPS: This module is included with the SQL Server installation (for backwards compatibility), but is no longer being updated. The most up-to-date PowerShell module is the SqlServer module. SqlServer: This module includes new cmdlets to support the latest SQL features. The module also contains updated versions of the cmdlets in SQLPS. https://learn.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-2017 – Kiquenet Nov 15 '18 at 07:41
  • Adding this line into my script fixed it for me: Import-Module Sqlps -DisableNameChecking; – Don Rolling Dec 11 '19 at 16:38
1

Maybe you can try the below Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 Although I notice those snap-ins are not installed on my local but I am able to use SQLPS but that could be because Execution policy is Unrestricted. Source : SQL Authority

Update : Download and Install in below order

  1. Microsoft® System CLR Types for Microsoft® SQL Server® 2012
  2. Microsoft® SQL Server® 2012 Shared Management Objects Microsoft®
  3. Windows PowerShell Extensions for Microsoft® SQL Server® 2012

Link - https://www.microsoft.com/en-us/download/details.aspx?id=29065

Vinay
  • 954
  • 8
  • 13
  • These commands aren't valid or available for SQL Server 2012. "Import-Module SQLPS" should be the same thing, but doesn't help in my case (nor in the case of the original poster). – DustinA Oct 23 '15 at 20:58
  • I apologize, but from the link it appeared the author is referring to SQL Server 2012 and has the same issue with Import-Module. – Vinay Oct 26 '15 at 14:36
  • Hey, I just realized I never got time to update the answer which worked for me. Hopefully this helps. – Vinay May 15 '17 at 23:09
1

I found myself in the same situation. There are two products: SQLPS module and SQLServer Module.

SQLPS is installed automatically when you install an older SQL client.

It can also be extracted from that installation and copied on another host if you cannot install SQL client there (PROD env. for example). You have here the steps in the "details" paragraph

SQLServer Module can be installed via powershell commands but it requires powershell v5 . see here details

TudorIftimie
  • 1,050
  • 11
  • 21