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.

by Sandro Pereira | Feb 2, 2021 | BizTalk Community Blogs via Syndication

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:pathpowershellscript.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.
PackageManagementInstall-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 FilesWindowsPowerShellModulesPowerShellGet1.0.0.1PSModule.psm1:7468 char:21
+ …     $null = PackageManagementInstall-PackageProvider -Name $script:N …
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (Microsoft.Power…PackageProvider:InstallPackageProvider) [Install-PackageProvider], Exception
    + FullyQualifiedErrorId : NoMatchFoundForProvider,Microsoft.PowerShell.PackageManagement.Cmdlets.InstallPackageProvider
PackageManagementImport-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 FilesWindowsPowerShellModulesPowerShellGet1.0.0.1PSModule.psm1:7474 char:21
+ …     $null = PackageManagementImport-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.
PackageManagementGet-PackageProvider : Unable to find package provider ‘NuGet’. It may not be imported yet. Try ‘Get-PackageProvider -ListAvailable’.
At C:Program FilesWindowsPowerShellModulesPowerShellGet1.0.0.1PSModule.psm1:7478 char:30
+ … tProvider = PackageManagementGet-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:tempSqlServer


Then, copy that whole directory to your target computer(s) to the following folder:

C:Program FilesWindowsPowerShellModules



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(";")


The post 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. appeared first on SANDRO PEREIRA BIZTALK BLOG.