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

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 25

DBAduck - Ben MillerPrincipal ConsultantCommented: 2015-02-27

Here is the part where you are missing it.

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

You need to do this instead:

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

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 8

Leo TorresSQL DeveloperAuthor Commented: 2015-02-28

that worked

Powershell SQL insert data into tables

Who is Participating?

Already a member? Login.