Invoke-Sqlcmd无法运行 [英] Invoke-Sqlcmd unable to run

问题描述

我有一个PowerShell脚本，该脚本检查运行该服务器的CPU的CPU级别，然后如果它超过某个阈值，它将运行SQL存储过程和电子邮件。

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.

该脚本在使用最新版PowerShell的开发服务器上正确运行。但是，我在从中获取CPU值的实时服务器上运行 Invoke-Sqlcmd 命令时遇到问题。例如，它可以称为LIVESERVER。
它正在运行PowerShell 2.0，我认为这是个问题：

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:

术语 Invoke-Sqlcmd不被认为是cmdlet，函数，脚本文件或可运行程序的名称。检查名称的拼写，或者是否包含路径，请验证路径是否正确，然后重试。

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.

请勿对此服务器进行任何更改，因为这对业务至关重要（除非这是在不重新启动的情况下更新PowerShell的简单任务，等等）。

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

是否可以运行我的服务器我的开发服务器上的脚本，并指定从中获取CPU数据的服务器？我不确定语法如何实现此目的。

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.

这是我的脚本：

# Email 
$recipients = @("Ricky <[e-mail]>")

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

推荐答案

PowerShell v2不会自动加载模块，因此您需要自己加载相关模块或管理单元：

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

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

[来源]

这篇关于Invoke-Sqlcmd无法运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！