A fish out of water: PowerShell – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program.

Posted: February 3, 2021  |  Categories: PowerShell

It has been a long time since I go off-topic on my main blog topic: Enterprise Integration. Nevertheless, everything that I wrote is somehow related to my daily job, and if you keep an eye on my blog, you will know that I like using PowerShell for scripting and automate several tasks.

Today while I was implementing a PowerShell script that I use in several clients I was surprised by this error: Invoke-Sqlcmd : The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program.

The full warning message would be:

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 C:\path\powershellscript.ps1:7 char:14

+ $sqlresult = Invoke-Sqlcmd -Query “SELECT TOP 1 …
+              ~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Invoke-Sqlcmd:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

The error message is very clear, and the problem is quite simple to solve… except if your machine doesn’t have connectivity to the internet. But let’s first explain the issue.

Cause

As I told you before, The error message is very clear, and the problem is quite simple to solve, the SQL Server PowerShell module is not installed on the machine.

Many may think that SQL Server Management Studio is a requirement or by installing it may solve this problem, but that is not true. In fact, SQL Server Management Studio (SSMS), doesn’t install the PowerShell module.

Solution 1: System with access to the Internet

If the machine where this issue is happening has access to the internet, then you just need to install the SQL Server PowerShell module by executing the following script:

Import-Module sqlserver

Note: You should run PowerShell as an Administrator

Solution 2: System without access to the Internet

However, if your machine doesn’t have access to the internet you will get the folowing error idf you try to execute the above script:

Unable to download from URI ‘https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409’ to ”.
Unable to download the list of available providers. Check your internet connection.
PackageManagement\Install-PackageProvider : No match was found for the specified search criteria for the provider ‘NuGet’. The package provider requires ‘PackageManagement’ and ‘Provider’
tags. Please check if the specified package has the tags.
At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:7468 char:21
+ …     $null = PackageManagement\Install-PackageProvider -Name $script:N …
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (Microsoft.Power…PackageProvider:InstallPackageProvider) [Install-PackageProvider], Exception
    + FullyQualifiedErrorId : NoMatchFoundForProvider,Microsoft.PowerShell.PackageManagement.Cmdlets.InstallPackageProvider
PackageManagement\Import-PackageProvider : No match was found for the specified search criteria and provider name ‘NuGet’. Try ‘Get-PackageProvider -ListAvailable’ to see if the provider exists on the system.
At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:7474 char:21
+ …     $null = PackageManagement\Import-PackageProvider -Name $script:Nu …
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (NuGet:String) [Import-PackageProvider], Exception
    + FullyQualifiedErrorId : NoMatchFoundForCriteria,Microsoft.PowerShell.PackageManagement.Cmdlets.ImportPackageProvider
Unable to download from URI ‘https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409’ to ”.
Unable to download the list of available providers. Check your internet connection.
PackageManagement\Get-PackageProvider : Unable to find package provider ‘NuGet’. It may not be imported yet. Try ‘Get-PackageProvider -ListAvailable’.
At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:7478 char:30
+ … tProvider = PackageManagement\Get-PackageProvider -Name $script:NuGet …
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Microsoft.Power…PackageProvider:GetPackageProvider) [Get-PackageProvider], Exception
    + FullyQualifiedErrorId : UnknownProviderFromActivatedList,Microsoft.PowerShell.PackageManagement.Cmdlets.GetPackageProvider

To solve this issue you need to:

  • From an Internet-connected computer, execute the following script
Save-Module sqlserver -path c:\temp

Note: You can set a different path to save the module.

  • The above command will save the SQL Server PowerShell module to the directory you set on a folder called SqlServer
    • In my case, c:\temp\SqlServer
  • Then, copy that whole directory to your target computer(s) to the following folder:
    • C:\Program Files\WindowsPowerShell\Modules

By coping to this folder it will make the module available to all users.

If you are not sure about the path, you can always check the paths by exxecuting the following script:

$env:psmodulepath.split(";")
#1 Azure Monitoring Platform

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

4 thoughts on “A fish out of water: PowerShell – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program.”

Leave a Reply

Your email address will not be published. Required fields are marked *

turbo360

Back to Top