CODEWITHSUNDEEP
powershellwindows-serverinvoke-sqlcmd
Gau
Gau

Reputation: 541

'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 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.

Upvotes: 44

Views: 140573

Answers (3)

KrumpiDumpi
KrumpiDumpi

Reputation: 1

I had similar problem, but servers where I needed to run my scripts had no access to internet. I found a simple .exe program, that can run queries in similar way.

https://github.com/SqlQuantumLeap/SimpleSqlExec

You just call this exe instead a Invoke-sqlcmd.

.\SimpleSqlExec -cs $ConnectionStringDB -Q $query

Hope it helps.

Upvotes: 0

Akash Masand
Akash Masand

Reputation: 1731

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 -Module sqlserver.

If this module is not readily available, you can Import-Module sqlserver after installing it.

Upvotes: 77

Ak02
Ak02

Reputation: 161

This is not a complete solution, but just a work around which is working for me.

When you execute the query from automation the user which is executing that is not having access to the sqlcmd. Execute you command for the directory where your sqlcmd.exe is present.

Just put

CD "C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn".

to get the location for sqlcmd search the location for SQLCMD.exe in the search box.

if not found, you need to install that where it is missing, but in your case I think it is present, you just need to get the location right.

Also you will need set the path variable for the user executing the automation script or else it will only recognize the sqlcmd, but wont execute that.

$env:Path   += ";C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\"

you can get this path from you local user for which it is working by $Env:Path

Upvotes: -1

Related Questions