Menu
We have recently started using SQL Server 2012 SP3 and building the SQL server 2012 using a PowerShell script. There is a requirement in our automation process to run multiple database scripts on a db and I have found Invoke-Sqlcmd very reliable until I found this issue.
When I run Invoke-sqlcmd with a proper set of parameters in PowerShell's 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 a 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 the below command in my script
get-pssnapin -Registered
Import-Module “sqlps” -DisableNameChecking**
Even after adding the above into the script, I still end up with same error. But when I run the same script manually it runs perfectly fine. I don't understand what is wrong.
PowerShell automation script - This script installs the .Net Framework 3.5, SQL Server 2012, SQL Server 2012 SP3, and then loads the SMO assembly that I use to change SQL settings such as the Max Memory limit of SQL.
579
The SQLServer module is the official SQL module developed by Microsoft. You can use the Invoke-SqlCmd cmdlet to query SQL using this module.
PowerShell features many one-line commands for working with SQL Server, one of which is Invoke-SqlCmd. This tool can be useful in many development contexts where we need to quickly execute scripts or test code and it helps to know some of the parameters we'll often use.
The default location of the sqlcmd utility is 'C:\Program Files\Microsoft SQL Server\150\Tools\Binn'.
The sqlcmd utility is a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks. To use sqlcmd interactively, or to build script files to be run using sqlcmd, users must understand Transact-SQL.
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.
88
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
