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

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]

Answer 2

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

Answer 3

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

Answer 4

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/

Answer 5

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.