Invoke-Sqlcmd' is not recognized as the name of a cmdlet
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 .
Answer
Open up PowerShell as an Administrator and install the sqlserver module by Install-Module sqlserver
After the module has installed, the module commands including the Invoke-sqlcmd should be readily available.
You can check the same using Get-Command -ModuleName sqlserver.
If this module is not readily available, you can Import-Module sqlserver after installing it.