We help IT Professionals succeed at work.

Powershell SQL insert data into tables

My insert is now working.

   
add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue

CLS
$Server = "Server"
$Qry = "Select Top 5 Name from NOCTools.dbo.ADComputers Where Left(Name,1) in ('E','A')"
$ServerList = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $Server -Query $Qry

Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $Server -Query "Truncate Table NOCTools.dbo.RebootReport"


 
foreach ($computer in $ServerList)
#$ServerList | Foreach-Object
{
    Try{
            $computer = $_.
            $Computerobj = "" | select ComputerName, Uptime, LastReboot
            $wmi = Get-WmiObject -ComputerName $computer -Query "SELECT LastBootUpTime FROM Win32_OperatingSystem" -EV Err -EA SilentlyContinue
            $now = Get-Date
            $boottime = $wmi.ConvertToDateTime($wmi.LastBootUpTime)
            $uptime = $now - $boottime
            $d = $uptime.days
            $h = $uptime.hours
            $m = $uptime.Minutes
            $s = $uptime.Seconds
            $Computerobj.ComputerName = $computer
            $Computerobj.Uptime = "$d Days $h Hours $m Min $s Sec"
            $Computerobj.LastReboot = $boottime
            $Computerobj 

$Qry2 = "INSERT INTO NOCTools.dbo.RebootReport(Computer,RebootDate,Uptime,[Action],Reason,ADUser,Process)
         VALUES ('$computer','$boottime','$d Days $h Hours $m Min $s Sec',NULL,NULL,NULL,NULL)"

Write-Host @Qry2
         Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $Server -Query "INSERT INTO NOCTools.dbo.RebootReport(Computer,RebootDate,Uptime,[Action],Reason,ADUser,Process)
         VALUES ('$computer','$boottime','$d Days $h Hours $m Min $s Sec',NULL,NULL,NULL,NULL)"


}
 
        Catch {
                Write-host $computer
           }

 }                 

Open in new window



Output
System.Data.DataRow
System.Data.DataRow
System.Data.DataRow
System.Data.DataRow
System.Data.DataRow
PS C:\Users\usgltorres> 

Open in new window


How do I query the actual elements in the dataset
Comment
Watch Question

Commented:
Here is the part where you are missing it.

foreach ($computer in $ServerList)
#$ServerList | Foreach-Object
{
    Try{
            $computer = $_.

Open in new window


You need to do this instead:

foreach ($row in $ServerList)
{
    Try{
            $computer = $row.Name

Open in new window


Invoke-SqlCmd actually returns a set of DataRow objects, so in that datarow object there are columns and you just reference them like so:

$row.Name
$row.FieldName
etc.

Author

Commented:
that worked