0

I am trying to get Remote Server hardware specs and would like to insert them into SQL Server table which is also on a remote server. I am getting the following error.

Invoke-SQLcmd : Incorrect syntax near '@'.
Msg 102, Level 15, State 1, Procedure, Line 5.

Code:

$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
}
}

I want to create a task which can execute the code get the configuration of Remote Servers and Insert the variables in SQL Server table after every 15 minute

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
StillLearning
  • 15
  • 1
  • 4
  • 1
    Take a look at the INSERT Query being generated and validate that it will run in Microsoft SQL Server Management Studio. The error indicates that there is something wrong in the query being run. – HAL9256 Apr 24 '19 at 19:55
  • 2
    You should really be parametrising your query too, not injecting the values. – Thom A Apr 24 '19 at 19:58
  • If you remark the Invoke-SQLcmd and add a write-line $insertquery there, does it run without issue? – UnhandledExcepSean Apr 24 '19 at 19:58

2 Answers2

0

  • In an expandable string ("...") you cannot directly embed property access expressions such as $ENTRY.QDate; instead you must enclose them in $(...):

    • For an overview of PowerShell's string expansion rules, see this answer.

    • Without the enclosing $(...), $ENTRY is stringified as a whole, which in the case of a [pscustomobject] instance results in a hashtable-like string representation that starts with @, which is what broke your query.

  • Since your property values seem to have embedded quoting already, there is no need to quote them inside the SQL string - leaving aside that '['...']' is not the correct way to quote string literals.

Therefore:

$insertquery="
INSERT INTO [dbo].[TESTTB01]
   (Date,SName,ACPU,MEMU,DRVC)
     VALUES
   ($($ENTRY.QDate), $($ENTRY.ServerName), $($Entry.CPULoad), $($Entry.MemLoad),$($Entry.CDrive))
GO

Taking a step back: Consider heeding Larnu's advice to parameterize your query instead of embedding values as literals in the query string.

mklement0
  • 382,024
  • 64
  • 607
  • 775
0

final code is 

$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
}
StillLearning
  • 15
  • 1
  • 4
  • 1
    Thanks for accepting. Given that your answer simply shows the accepted answer in the context of your code and given that the specifics of your code are unlikely to be of interest to future readers, I think the accepted answer is sufficient. Therefore, please consider deleting this answer. Also, in future questions, please consider reducing the amount of code you post, by striving to provide an [MCVE (Minimal, Complete, and Verifiable Example)](http://stackoverflow.com/help/mcve). – mklement0 Apr 25 '19 at 15:07