Invoke-Sqlcmd unable to run

Question

I have a PowerShell script that checks the CPU level of the server it is running on, and then if it is above a certain threshold it will run a SQL stored procedure and e-mail.

The script runs correctly on my development server with the latest version of PowerShell. However, I am having issues running the Invoke-Sqlcmd command on the live server I need to get the CPU values from. For sake of example it can be called LIVESERVER. It is running PowerShell 2.0 which I think is the issue:

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 would rather not make any changes to this server as it is business critical (unless this is an easy task to update PowerShell without restarts, etc.).

Is it possible to run my script from my development server and specify the server to get the CPU data from? I'm not sure of how the syntax would work to achieve this.

Here is my script:

# Email 
$recipients = @("Ricky <ricky@email.com>")

# Loop 20 times
for ($i= 1; $i -le 20; $i++) {
    # Find CPU average usage percentage for given time period
    $cpuutil = (Get-Counter -Counter "\Processor(_Total)\% Processor Time" -SampleInterval 1 -MaxSamples 20 |
               select -ExpandProperty countersamples |
               select -ExpandProperty cookedvalue |
               Measure-Object -Average).Average

    # Display average CP output
    $cpuutil

    # If CPU average percentage is higher than given value, run stored procedure and
    # e-mail to notify
    if ($cpuutil -ge 60) {
        Invoke-Sqlcmd -Query "SELECT * FROM [Vehicle].[dbo].[tblIndicator]" -ServerInstance "LIVESERVER"
    }

    if ($cpuutil -ge 60) {
        Send-MailMessage -From "serverchecks@email.com" -To $recipients -Body "SP Ran" -Subject "Stored Procedure" -Dno onSuccess, onFailure -SmtpServer 111.1.1.111
    } else {
        Start-Sleep -s 10
    }
}

Does this answer your question? [Invoke-Sqlcmd' is not recognized as the name of a cmdlet](https://stackoverflow.com/questions/37825030/invoke-sqlcmd-is-not-recognized-as-the-name-of-a-cmdlet) — Michael Freidgeim, May 11 '21 at 01:20

score 14 · Answer 1 · answered Jun 10 '19 at 15:03

14

If import-module fails then you'll need to run install-module first. Installing SSMS or SSDT doesn't include sqlserver module by default.

install-module sqlserver
update-module sqlserver
import-module sqlserver

answered Jun 10 '19 at 15:03

Brent

1,378
2
16
30

score 11 · Answer 2 · answered Mar 13 '17 at 12:11

This should help your debugging!

if (-not (Get-Command Invoke-Sqlcmd -ErrorAction SilentlyContinue)) {
    Write-Error "Unabled to find Invoke-SqlCmd cmdlet"
}

if (-not (Get-Module -Name SqlServer | Where-Object {$_.ExportedCommands.Count -gt 0})) {
    Write-Error "The SqlServer module is not loaded"
}

if (-not (Get-Module -ListAvailable | Where-Object Name -eq SqlServer)) {
    Write-Error "Can't find the SqlServer module"
}

Import-Module SqlServer -ErrorAction Stop

score 8 · Answer 3 · answered Mar 13 '17 at 12:13

8

Make sure you've installed Powershell module with SQL Server:

Then try to import module SQLPS:

Import-Module SQLPS

See also: https://blogs.msdn.microsoft.com/psssql/2008/09/02/sql-server-2008-management-tools-basic-vs-complete-explained/

answered Mar 13 '17 at 12:13

Paweł Dyl

8,888
1
11
27

Ansgar Wiechers · Accepted Answer · 2019-05-21T11:20:03.670

7

PowerShell v2 doesn't auto-load modules, so you need to load the relevant modules or snapins yourself:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

[Source]

edited May 21 '19 at 11:20

answered Mar 13 '17 at 12:16

Ansgar Wiechers

193,178
25
254
328

That worked for me, I must have previously loaded the wrong snapins as I had errors trying something similar. Thanks! – Ricky Mar 13 '17 at 12:42
If you're still get an error and Sql Server is not installed in your machine, you can install [SqlServer Powershell](https://www.powershellgallery.com/packages/Sqlserver/21.1.18147). This work for me – Umut Çömlekçioğlu Jul 31 '19 at 05:28

score -2 · Answer 5 · edited Jun 14 '21 at 14:22

-2

Spent almost one hour on this issue. I had the below error message when I was running the scripts using MS Build.

SQLCMD : The term 'SQLCMD' is not recognized as the name of a cmdlet, function, script file, or operable program.

I finally found out that restarting the windows server after SQL server installation did the magic. Now my scripts are running smooth.

edited Jun 14 '21 at 14:22

Dharman

30,962
25
85
135

answered Jun 14 '21 at 14:17

Balasubramanian S

1,345
12
16

Invoke-Sqlcmd unable to run

5 Answers5

Linked