It’s always a fun to learn and share new thing which I learn about any technology. So I decided to spend my weekend at home quietly, so I could learn some scripting using PowerShell and SQL Server. My laptop has tons of software installed so I always use my Virtual Machine to learn new things because I want to see how the things work when someone is working as a learner. My day didn’t start well as I was hit by errors as soon as I started Invoke-SQLCMD. This is part of PowerShell command and is worth a look.
This is the first error I got as soon as I wanted to run Invoke-SQLCMD
Error # 1 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.
The Error was because of the fact that I just installed SQL Server Engine on the machine. I didn’t install any client components. So essentially the error appears because the Invoke-sqlcmd cmdlet is not included as part of Windows PowerShell, but instead it is of sqlps (SQL Server 2008 PowerShell Host). So, before using invoke-sqlcmd we should install SSMS or the SQL Server Feature Pack (latest is SQL 2014 which can be downloaded from here)
Once installation was done, I ran the command and I got a new error.
Error # 2 – The ‘invoke-sqlcmd‘ command was found in the module ‘SQLPS’, but the module could not be loaded. For more information, run ‘Import-Module SQLPS’.
Here is the complete error message
As we can see above, I can also run Import-Module SQLPS to know more. Here is the output of the command
Error # 3 Import-Module : File E:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_execution_policies?view=powershell-5.1.
Here is the complete error message
Why this error? If you know the basics of PowerShell, every command is called cmdlet (pronounced as command let). Invoke-SQLCmd is also a cmdlet provided either by the snap-in SqlServerCmdletSnapin100 (prior to SQL Server 2012) or module SQLPS (SQL Server 2012+). So, we need any one loaded into PowerShell (for example, at the beginning of your script) before you can call the cmdlet.
Assuming PowerShell is installed on the SQL server. You can open the Windows PowerShell Command prompt as below and get on to SQL power shell environment.
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Once I followed above, I was able to use Invoke-SQLCMD as below
Hope this would help others in finding the solution to the errors. How many of you here are frequent users of PowerShell to administer and automate SQL Server tasks?
Reference: Pinal Dave (https://blog.sqlauthority.com)
14 Comments. Leave new
Good article, it works for me
Hi Pinal,
I am getting this error for this, could you please provide me alternate solution.
PS H:\> Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin : No snap-ins have been registered for Windows PowerShell version 2.
At line:1 char:13
+ Add-PSSnapin <<<< SqlServerProviderSnapin100
please ensure SqlServer module is installed.. install-module -Name SqlServer
Hi,
We have recently started using Sql server 2012 SP3 and building the SQL server 2012 using the powershell script. There is a requirement in our automation to run multiple Database script on the db and I found Invoke-Sqlcmd very reliable until I found this issue.
When I run the Invoke-sqlcmd with proper set of parameter in a powershell debug mode on the System on which the SQL server is installed recently. I don’t have problem.
PowershellCommand : Invoke-Sqlcmd -InputFile $sStrJBSPExecRolePath -ServerInstance $sStrSQLName -ErrorAction Stop
But when I execute same query through powershell automation script after rebuilding the same Server I end up getting below error
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.
I did research online many suggested to Import sqlps etc so for testing I added below in my script
get-pssnapin -Registered
Import-Module “sqlps” -DisableNameChecking
Even after adding above in the script . I still end up with same error. But when I run the same script manually it runs perfectly fine . I couldn’t understand what can be wrong.
powershell automation script – This script installs the .Net Framework 3.5 ,SQL Server 2012 ,SQL server 2012 SP3 and then load the smo assembly that I use to change sql settings such as Max Memory limit of SQL .
We have recently started using Sql server 2012 SP3 and building the SQL server 2012 using the powershell script. There is a requirement in our automation to run multiple Database script on the db and I found Invoke-Sqlcmd very reliable until I found this issue.
When I run the Invoke-sqlcmd with proper set of parameter in a powershell debug mode on the System on which the SQL server is installed recently. I don’t have problem.
PowershellCommand : Invoke-Sqlcmd -InputFile $sStrJBSPExecRolePath -ServerInstance $sStrSQLName -ErrorAction Stop
But when I execute same query through powershell automation script after rebuilding the same Server I end up getting below error
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.
I did research online many suggested to Import sqlps etc so for testing I added below in my script
get-pssnapin -Registered
Import-Module “sqlps” -DisableNameChecking
Even after adding above in the script . I still end up with same error. But when I run the same script manually it runs perfectly fine . I couldn’t understand what can be wrong.
powershell automation script – This script installs the .Net Framework 3.5 ,SQL Server 2012 ,SQL server 2012 SP3 and then load the smo assembly that I use to change sql settings such as Max Memory limit of SQL .
I am running powershell on the server and also running the SQL server with Local System.
Awesome! Great Post!
Love you!!
me too.
i am getting Invoke-sqlcmd error, if I cal my powershell script from BMC BDA tools. But is i run it separately, the executes properly. what can be the issue?
I tried this on powershell 6 and still unable to get it working. I keep getting error saying “Invoke-sqlcmd” is not recognized as the name of cmdlet.
I did notice that when you install PS 6 , it creates new folder. Instead of going to WindowsPowerShell , now you will have PowerShell folder with modules being loaded there if you install it.
Even though I installed SQlServer module it shows in Powershell/modules folder instead of WindowsPowerShell module ? I am wondering if that is something that is confusing the system ?
It has 10 years now, your articles are still my favourite.
Thank you Divya!
Hi,
I have create a jams task scheduler job for sharing the report to client. The term ‘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.I am getting this error. Could you please help me in this error.
param (
[string]$DatabaseServer = “COREP02P.centiv.com”,
[string]$SFTPPath = “/home/cdibrm02/”,
[string]$SFTPServer = “test-externalftp.prudential.com”,
[string]$SFTPCredential = “PrudentiaReport-SFTP-Test”,
[string]$JamsServerName = “jams01d.brandmuscle.local”
)
Import-Module JAMS
$localDataDirectory = “C:temp$(New-Guid)”
New-Item $localDataDirectory -itemType directory
$Prudential_EmailCampaignReport = “$localDataDirectoryPrudential_EmailCampaignReport.txt”
$Prudential_EmailCampaignReportFile = “$localDataDirectoryPrudential_EmailCampaignReportFile.txt”
# run reports
sqlcmd -S $Databaseserver -E -d “CentivPos” -Q “Exec [dbo].[Prudential_EmailCampaignReport]” -o $Prudential_EmailCampaignReport -W -s “,” -h -1
(get-content $Prudential_EmailCampaignReport | select -Skip 1) | select-string -pattern “Warning” -notmatch | select-string -pattern “affected” -notmatch | select-string -pattern “——–” -notmatch | set-content $Prudential_EmailCampaignReport
$Data = Get-Content $Prudential_EmailCampaignReport -Raw
$Cleanup = $Data.Replace(“`n`r`n”,””).Replace(“`n`n”,””).Replace(“`r”,””)
$Cleanup = $Cleanup | Set-Content $Prudential_EmailCampaignReport -Force
Hi Pinal, I am getting error while running a command in power shell. Can you please help. and here is the error message:
Run : The term ‘Run’ 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 line:1 char:1
+ Run Invoke-Sqlcmd
+ ~~~
+ CategoryInfo : ObjectNotFound: (Run:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException