sql - Invoke-SQLcmd：“@”附近的语法不正确。消息 102，级别 15，状态 1，程序，第 5 行

Question

我正在尝试获取远程服务器硬件规格，并希望将它们插入同样位于远程服务器上的 SQL Server 表中。我收到以下错误。

Invoke-SQLcmd：“@”附近的语法不正确。
消息 102，级别 15，状态 1，程序，第 5 行。

代码：

$ServerName = "SQLSRV"
$DatabaseName = "Automation"
$conn=New-Object System.Data.SqlClient.SQLConnection
$ConnectionString =  "Server = $SQLServer; Database = $SQLDBName; 
User ID= automationusr; Password= Password@SQL" 
$conn.ConnectionString=$ConnectionString
$conn.Open()
$ServerListFile = "C:\Scripts\ServerList.txt"
$ServerList = Get-Content $ServerListFile -ErrorAction SilentlyContinue  
$Result = @()  
ForEach($computername in $ServerList)  
{ 

$AVGProc = Get-WmiObject -computername $computername win32_processor |  
Measure-Object -property LoadPercentage -Average | Select Average 
$OS = gwmi -Class win32_operatingsystem -computername $computername | 
Select-Object @{Name = "MemoryUsage"; Expression = {“{0:N2}” -f ((($_.TotalVisibleMemorySize - $_.FreePhysicalMemory)*100)/ $_.TotalVisibleMemorySize) }} 
$vol = Get-WmiObject -Class win32_Volume -ComputerName $computername -Filter "DriveLetter = 'C:'" | 
Select-object @{Name = "C PercentFree"; Expression = {“{0:N2}” -f  (($_.FreeSpace / $_.Capacity)*100) } } 
$result += [PSCustomObject] @{  
ServerName = "'$computername'" 
CPULoad = "'$($AVGProc.Average)%'" 
MemLoad = "'$($OS.MemoryUsage)%'" 
CDrive = "'$($vol.'C PercentFree')%'" 
QDate = "'$(Get-Date -Format "yyyyMMdd")'"
    } 

    Foreach($Entry in $Result)  
{
$QDate=$TESTTB01.Date
$ServerName=$TESTTB01.SName
$CPULoad=$TESTTB01.ACPU
$MemLoad=$TESTTB01.MEMU
$CDrive=$TESTTB01.DRVC

$insertquery="
INSERT INTO [dbo].[TESTTB01]
   (Date,SName,ACPU,MEMU,DRVC)
     VALUES
   ('['$ENTRY.QDate']','['$ENTRY.ServerName']','['$Entry.CPULoad']','['$Entry.MemLoad']','['$Entry.CDrive']')
GO
"
Invoke-SQLcmd -ServerInstance 'SQLSRV' -query $insertquery -U automationusr -P Password@SQL -Database Automation
}
}

我想创建一个任务，它可以执行代码获取远程服务器的配置并在每 15 分钟后在 SQL Server 表中插入变量

标签： sqlsql-serverpowershell

Answer 1

最终代码是

$ServerName = "SQLSRV"
$DatabaseName = "Automation"
$conn=New-Object System.Data.SqlClient.SQLConnection
$ConnectionString =  "Server = $SQLServer; Database = $SQLDBName; 
User ID= automationusr; Password= Password@SQL" 
$conn.ConnectionString=$ConnectionString
$conn.Open()
$ServerListFile = "C:\Scripts\ServerList.txt"
$ServerList = Get-Content $ServerListFile -ErrorAction SilentlyContinue  
$Result = @()  
ForEach($computername in $ServerList)  
{ 

$AVGProc = Get-WmiObject -computername $computername win32_processor |  
Measure-Object -property LoadPercentage -Average | Select Average 
$OS = gwmi -Class win32_operatingsystem -computername $computername | 
Select-Object @{Name = "MemoryUsage"; Expression = {“{0:N2}” -f ((($_.TotalVisibleMemorySize - $_.FreePhysicalMemory)*100)/ $_.TotalVisibleMemorySize) }} 
$vol = Get-WmiObject -Class win32_Volume -ComputerName $computername -Filter "DriveLetter = 'C:'" | 
Select-object @{Name = "C PercentFree"; Expression = {“{0:N2}” -f  (($_.FreeSpace / $_.Capacity)*100) } } 
$result += [PSCustomObject] @{  
ServerName = "'$computername'" 
CPULoad = "'$($AVGProc.Average)%'" 
MemLoad = "'$($OS.MemoryUsage)%'" 
CDrive = "'$($vol.'C PercentFree')%'" 
QDate = "'$(Get-Date -Format "yyyyMMdd hh:MM:ss tt")'"
    } 

    Foreach($Entry in $Result)  
{
$QDate=$TESTTB01.Date
$ServerName=$TESTTB01.SName
$CPULoad=$TESTTB01.ACPU
$MemLoad=$TESTTB01.MEMU
$CDrive=$TESTTB01.DRVC

}
$insertquery="
INSERT INTO [dbo].[TESTTB01]
   (Date,SName,ACPU,MEMU,DRVC)
     VALUES
   ($($ENTRY.QDate), $($ENTRY.ServerName), $($Entry.CPULoad), $($Entry.MemLoad),$($Entry.CDrive))
GO
"
Invoke-SQLcmd -ServerInstance 'SQL01' -query $insertquery -U automationusr -P Password@SQL -Database Automation
}