33

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.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Gau
  • 431
  • 1
  • 5
  • 4
  • consider posting your script. are you perhaps running it as Local-System when it fails? – No Refunds No Returns Jun 15 '16 at 02:30
  • Yes the SQL server service is running under Local-System. But when I run the powershell manually in debug mode it runs without any issue. sure I can put the part of the script $sStrSQLName = $env:COMPUTERNAME $sStrSPWIAFilePath = "D:\_ISD\Installs\Scripts\SQL_Install\SQL Scripts\sp_WhoIsActive.sql" Invoke-Sqlcmd -InputFile $sStrSPWIAFilePath -ServerInstance $sStrSQLName -ErrorAction Stop – Gau Jun 15 '16 at 10:29
  • are you running the script as part of an automated process and that is when you see the failure? Your user profile may not be the same user profile that is being used when you see the failure. Can you use RUNAS or PSEXEC to start a session in the context of the user where your script fails. Alternately can you run your process with your credentials if it works for you. – No Refunds No Returns Jun 17 '16 at 13:09

2 Answers2

61

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.

mskfisher
  • 3,291
  • 4
  • 35
  • 48
Akash Masand
  • 1,441
  • 14
  • 30
0

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

TylerH
  • 20,799
  • 66
  • 75
  • 101
Ak02
  • 143
  • 4
  • 16