Post reply

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

  • 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!

  • 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.

  • 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!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply