Writing Table to SQL Database

[aludden]

I am trying to write the results of the following powershell script to a SGL database. The $DBROW has the correct values in it. However, I cannot figure out how to get the data into SQL??

Code: Select all

foreach ($server in $servers)
  {
    foreach ($log in $logs)
      {
        $SysEvent = New-Object system.diagnostics.eventLog($log, $server)
        $SysByDate = $SysEvent.entries[($SysEvent.Entries.count -1)..($SysEvent.Entries.count -2000)] | Where-Object { ($_.entryType -eq "Error" -or $_.entryType -eq "Warning") -and $_.timewritten -ge $last_ran_date }
        $DBRow = $SysByDate | sort timewritten -descending | Format-Table EventID, Source, EntryType, TimeWritten, UserName, MachineName, Message -HideTableHeaders -auto
        $DBRow
        $conn = New-Object system.data.sqlclient.sqlconnection
        $conn.connectionstring = "Server=ogh-teledb;Database=Servers;Trusted_Connection=yes;"
        $conn.open()

        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandText = "INSERT INTO Events (EventID,Source,EntryType,TimeWritten,UserName,MachineName,Message) VALUES $DBRow"
        $SqlCmd.Connection = $conn
        $SqlCmd.executenonquery()
        $conn.Close()
        

      }
  } 

[aludden]

I am trying to write the results of the following powershell script to a SGL database. The $DBROW has the correct values in it. However, I cannot figure out how to get the data into SQL??

Code: Select all

foreach ($server in $servers)
  {
    foreach ($log in $logs)
      {
        $SysEvent = New-Object system.diagnostics.eventLog($log, $server)
        $SysByDate = $SysEvent.entries[($SysEvent.Entries.count -1)..($SysEvent.Entries.count -2000)] | Where-Object { ($_.entryType -eq "Error" -or $_.entryType -eq "Warning") -and $_.timewritten -ge $last_ran_date }
        $DBRow = $SysByDate | sort timewritten -descending | Format-Table EventID, Source, EntryType, TimeWritten, UserName, MachineName, Message -HideTableHeaders -auto
        $DBRow
        $conn = New-Object system.data.sqlclient.sqlconnection
        $conn.connectionstring = "Server=ogh-teledb;Database=Servers;Trusted_Connection=yes;"
        $conn.open()

        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandText = "INSERT INTO Events (EventID,Source,EntryType,TimeWritten,UserName,MachineName,Message) VALUES $DBRow"
        $SqlCmd.Connection = $conn
        $SqlCmd.executenonquery()
        $conn.Close()
        

      }
  } 

[jhicks]

I think the Format-Table cmdlet is the culprit. This cmdlet tells PowerShell how to display information to the screen. When you are using it you are losing the original objects. In this case all you want are the string values. use Select-Object instaled of format-table. Drop the -auto parameter as well because you aren't formatting data.

[jvierra]

You will also not be able to "auto" enumerate the values as it is not a pure uni-dimensional array. You will need to do something like this:

Code: Select all

	
        $DBRow | % {    
            $cmd = "INSERT INTO dbo.ApplicationLog (EventID) VALUES(" + $_.EventID + ")"
            $cmd
        $SqlCmd.CommandText = $cmd
         $SqlCmd.executenonquery()
           }