How can i get properties of an object in order / variable substitution question

• schleep

  SSChampion

  Points: 12586

  More actions

  June 20, 2017 at 9:20 am

  #326836

  Hi all,

  I have an object, $Report, with 5 properties. Its an Array of datarows, returned from an SQL Query.
  The columns are as follows: FirstCol, SecondCol, ThirdCol, FourthCol, FifthCol

  When i do a get-member -membertype property, they are returned in alphabetical order.
  FifthCol
  FirstCol
  FourthCol
  SecondCol
  ThirdCol

  I can deal with this IFF there is a way to select from the array in that order, OR there is a way to list the members in the order returned from the query, OR I can determine the column index of a particular column.
  The goal here is to be able to run any query against n servers and dump the results into a single table on another server, without needing to know the column names and their data types.
  I have the table creation part handled, it's just the ordering of the column in the create or the ordering of the columns in the select.

  Hopefully this is clear enough.
  $Query1 is generated dynamically:
  $Query1 = "INSERT tmp_results VALUES ($_.cFiscal, $_.nActivities, $_.nCalls, $_.nNodeType_Id, $_.nNode_Id)"
  I'm expecting variable substitution to occur here, but
  $Report | %{$Query = "$Query1"; $objDB.ExecuteNonQuery($Query)}
  generates this call to SQL Server:
  INSERT tmp_results VALUES (.cFiscal, .nActivities, .nCalls, .nNodeType_Id, .nNode_Id)

  and,
  $Query1 = 'INSERT tmp_results VALUES ($($_.cFiscal), $($_.nActivities), $($_.nCalls), $($_.nNodeType_Id), $($_.nNode_Id))'
$Report1 | %{$Query = "$Query1"; $objDB.ExecuteNonQuery($Query)}
  results in
  INSERT tmp_results VALUES ($($_.cFiscal), $($_.nActivities), $($_.nCalls), $($_.nNodeType_Id), $($_.nNode_Id))

  Thanks!

• schleep

  SSChampion

  Points: 12586

  More actions

  June 21, 2017 at 6:58 am

  #1947544

  Solution: need to get the column info from the DataSet.Tables object.

  $Data = $objDB.ExecuteWithResults($Query)
# This gets the columns in order
$Data.tables[0].columns | sort -Property ordinal | select ordinal, ColumnName, DataType
# This gets the data
$ReportData = $Data.tables[0]

  I don't quite  understand why the columns property doesn't go to the $ReportData object, but there you have it.

  However, now that I can create the table in SQL Server with the columns in correct order, I can pass the results to the Write-DataTable cmdlet.

• schleep

  SSChampion

  Points: 12586

  More actions

  June 21, 2017 at 7:34 am

  #1947557

  See also: https://www.reddit.com/r/PowerShell/comments/4eylez/converting_sql_data_types_to_system_data_types/
  Which nicely wraps things up!