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 <[email protected]>")

# 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 "[email protected]" -To $recipients -Body "SP Ran" -Subject "Stored Procedure" -Dno onSuccess, onFailure -SmtpServer 111.1.1.111
    } else {
        Start-Sleep -s 10
    }
}

Answer 1

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